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
ID RecNo field1 field2
1 1 *** ***
1 2 *** ***
2 1 *** ***
3 1 *** ***
3 2 *** ***
3 3 *** ***
3 4 *** ***
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...