Post reply

updating 2 or more columns, using a join

  • October 8, 2008 at 6:16 am

    #193987

    i have one table with the following scheema:

    Table1

    ContractorID,

    OrganizationID,

    ContactID

    I have another table thats a back up of a certain number of rows from the above table, in the exact same format.

    Im testing an update to Table1. during my testing i have to rollback the changes after the users test it, so that i can make changes if appriopriate.

    currently my solution is a select into and two update statements

    --get original values stored in temp table

    select contractorID, organizationid, contactid into temptable

    where organizationid = @SomeValue

    and ContractID = @somevalue

    --update organization back to original value

    update t1

    set t1.organizationid = t.organizationid from tempTable as t join Table1 as t1 on t.contractorID = t1.contractorID

    --update contractor back to original value

    update t1

    set t1.contactid = t.contactid from tempTable as t join

    is it possible to do the two updates in one statement?

  • October 8, 2008 at 7:01 am

    #882228

    Any reason something like this wouldn't work?

    UPDATE Table1

    SET organizationid = t.organizationid ,

    contactid = t.contactid

    FROM tempTable AS t join Table1 AS t1 ON t.contractorID = t1.contractorID

    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]

  • October 8, 2008 at 2:55 pm

    #882597

    You can update 1 TABLE at a time, not more than 1 in an UPDATE statement

    But for that table, you can update from 1-N columns

    so Garadin's solution should work

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • October 9, 2008 at 2:30 am

    #882743

    thanks folks. as always, the solution is staring me in the face yet i miss it. much appriciated.

Viewing 4 posts - 1 through 3 (of 3 total)

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