How to update multiple tables in sql server 2008 ?

Answered How to update multiple tables in sql server 2008 ?

  • Friday, March 08, 2013 1:12 PM
     
     

    Hi All,

    I want to update multiple columns from multiple tables in a single UPDATE Query...

    Does Sql Server 2008 provide any mechanism to do so?

    If Sql Server 2008 provide such nice approach, please share some links with me!

    Thanks!


    • Edited by Maggy111 Friday, March 08, 2013 1:12 PM
    •  

All Replies

  • Friday, March 08, 2013 1:21 PM
     
      Has Code

    Try this

    UPDATE Table1, Table2
    SET Table1.Column1 = 'one' 
    ,Table2.Column2 = 'two'
    FROM Table1 T1, Table2 T2
    WHERE T1.id = T2.id
    and T1.id = 'id1'

  • Friday, March 08, 2013 1:28 PM
     
     Answered Has Code

    Try this

    UPDATE Table1, Table2
    SET Table1.Column1 = 'one' 
    ,Table2.Column2 = 'two'
    FROM Table1 T1, Table2 T2
    WHERE T1.id = T2.id
    and T1.id = 'id1'

    This is absolutely wrong... don't mislead people ..

    You can not update multiple table in one single update statement..  what you can do is wrap the update statement in a transaction, commit changes only when both update are successful

    e.g

    begin try

    begin tran

    update table1 set c1=2

    update  table2 set c1=2 

    commit

    end try 

    begin catch

    rollback

    end catch

    vt

     

     

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed As Answer by Naarasimha Saturday, March 09, 2013 1:03 AM
    • Marked As Answer by Maggy111 Monday, March 11, 2013 2:02 PM
    •  
  • Friday, March 08, 2013 1:31 PM
     
     Answered
    • Edited by Dineshkumar Friday, March 08, 2013 1:35 PM
    • Marked As Answer by Maggy111 Monday, March 11, 2013 2:03 PM
    •  
  • Friday, March 08, 2013 1:47 PM
     
     

    Thanks,

    But its not going to work!

  • Saturday, March 09, 2013 1:11 AM
     
     Answered

    You can't update 2 tables in a Single statement.

    http://stackoverflow.com/questions/5252802/how-to-use-update-trigger-to-update-another-table


    Narsimha

    • Marked As Answer by Maggy111 Monday, March 11, 2013 2:02 PM
    •  
  • Saturday, March 09, 2013 2:44 AM
     
     Answered
    OF COURSE NOT! Think about cascading DRI actions and how a set-oriented language has to work. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked As Answer by Maggy111 Monday, March 11, 2013 2:03 PM
    •