Needs help to update multilple records in a table which has a composite PK

  • 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...

  • 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.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply