October 10, 2008 at 1:05 am
Hi All,
I want to update multiple records in a table which has a composite primary key.
combination of ID and RecNo forms PK. One ID can have multiple RecNos. Sample data in my table is
IDRecNofield1field2
11******
12******
21******
31******
32******
33******
34******
For single row I can update like this
UPDATE table_name SET field1=value1,field2=value2
WHERE ID=@ID AND RecNo=@RecNo
//where value1 and value2 are some constants
But I have to update multiple records with same value1 and value2. I planned to create a temptable with Id and RecNo as columns, then by looping through temptable I want to update the table.
From .net Application, I am passing @IdList and @RecNoList to a stored procedure in comma separated form. For example if I want to update all the above records then @IdList will be '1,1,2,3,3,3,3' and @RecNoList will be '1,2,1,1,2,3,4'
I am using sql server 2000.
Please suggest me whether I am going in the right way?
Inputs are welcome...
October 10, 2008 at 5:52 am
Go ahead and create your temp table, but then instead of a loop, use this:
[font="Courier New"]UPDATE table_name
SET field1=T2.value1,
field2=T2.value2
FROM table_name T1 INNER JOIN temp_table T2 ON T1.ID = T2.ID AND T1.RecNo = T2.RecNo[/font]
Most likely the value 1 and value 2 would come from your temp table, which is why I aliased them as such.
Also, this case was pretty simple, but for the future, please reference the link in my signature for the preferred way to post table data here. If this solution does not work for you, please post your current data in this format.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply