In general, I try to avoid cursors; as a generalization I would suggest that set-based operations are usually faster than record-based operations. If you need to use a cursor, look up cursors in books online. What specifically are you trying to do?
You might want to give a look to this article about a TABLE OF NUMBERS; at times, this is useful:
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
Another thing is that many things can be "iterated through" just simply by joining one table to another.
|||If you are really going to use a cursor, look at books online under "FETCH". It has a full set of examples.|||Well i have a column called sort_order and the sort_order should be 12345678910 etc.... but the sort order got jacked up not its like 112233445668899 so I wanted to write a cursor to go through every row and where there is an duplicate replace it with the appropiate count.....Do you have a better way to do this?|||My knee-jerk reaction to this partial description is that a cursor is not best. How are you planning on computing the new sort_id if you have a duplicate sort id?|||Well what happen is that my sort_order column got thrown off due to some other issues....the computing of the sorting is correct when new items that need to sorted are created....its just we have some exsisting items that sort_order is thrown off due to other issues...Thats why i wanted to create a cursor to get all the exsisting sort orders and correct them by executing a sql script that use a cursor to iterate over all the ones who sort order is screwed to and correct them....does that make more sense? what would you suggest....I have an update query right now that will reset a particular set of items sort orders back to normal and everything is great but i think that take too long to do for all the sets of items that i have that need to be sorted.....what do think?
Now when i run this against a set of items that sort is off this will set the sort back to 12345678910111213 etc..... but i have alot of items to do this with, and that was the reason for the idea of creating the cursor.....would i be able to do this another way?
Declare @.intcounter int
set @.intcounter = 0
UPDATE tableName
SET @.intcounter = sort_order = @.intcounter + 1
FROM TableName AS st LEFT OUTER JOIN
TableName1 ON st.id = TableName.ID
WHERE (st.is_active = 1) AND (TableName1.ID = 13132)
|||I am puzzling over the LEFT OUTER JOIN with the WHERE ... AND (TableName1.ID = 13132). Doesn't the AND condition force this into basically being an INNER JOIN?
Also, since the only contribution from TableName1 is to filter based on ID, do you even need to join to this table? Can't you just filter based on ST.ID = 13132?
If you don't have to join to the OUTER (or maybe INNER) table, this should be a rather efficient update -- provided you have an index on the ID column.
Please check me on this.
|||This was part of a bigger Sql statement that i had executed...As you can see I am not a sql GURU...lol but It works for what i need it to do...I just need it to work for every indiviudal ID...I dont want to have to enter in all the different ID's to fix the sort order...thats why i thought to use the cursor to fix this issue.....cause from what i see i can get it to iterate over all the rows and update the sort order for the ID in a table....|||Consider doing it like this:
Declare @.intcounter int
set @.intcounter = 0UPDATE tableName
SET @.intcounter = @.intcounter + 1,
sort_order = @.intcounter
WHERE is_active = 1
AND ID = 13132
This should iterate through the records you want fine and provide a sequence of number 1-N for N records that meet the WHERE / AND criteria. It will be efficient PROVIDED that you have an index on the ID column.
|||I guess I sort-of need to make a confession here, too: I tend to loathe cursors -- I guess it is kind of a personal prejudice, but in this case I definitely have a bias and it is probably better if you are aware of that bias. I'd rather not go into that bias, but it is better for you if you know that this particular bias is present.
|||
Kent
I have one more question........I have a table with two columns..... the id column is not a primary key
column1ID column2_Numbers
1 2
1 2
1 3
1 4
1 5
1 6
1 7
In this table you see id 1 has some numbers that are duplicated......I want to know if this table was filled with a set of ids that have these numbers associated with them how would i go about counting all the IDS that had duplicate numbers associated with them? like since ID 1 has dups then my count should increase to 1 and so on?
|||One way to count the number of column1IDs that have duplicates is something like:
|||select count(*)
from ( select column1ID,
count(*) as idCount
from yourTable
group by columnID
having count(*) > 1
) x
what does the "x" do and why would i use the Having clause instead of doing it like
Select Count(id) From tableName
Where number_column = number_column?
|||The "x" is arbitrary; you must give an arbitrary name to a derived table (the expression in the parends); I just happened to choose "x"; it could be any name. I am considering the "number_column" issue; that looks like something i do not yet understand.
Yes, that is definitely a misunderstanding by me. My last SELECT code is wrong; sorry, I will see about fixing that.
|||I think it needs to look more like:
select count(*)
from ( select column1ID,
column2_numbers,
count(*) as idCount
from yourTable
group by columnID, column2_numbers
having count(*) > 1
) Y
The code:
Select Count(id) From tableName
Where number_column = number_column?
will not work as you wish; this code will select all rows of the table where NUMBER_COLUMN is not null
I suppose if you are wanting to find the NUMBER_COLUMN for a given ID you can do:
select column2_numbers,
count(*) as idCount
from yourTable
where column1ID = 1
group by column2_numbers
having count(*) > 1
No comments:
Post a Comment