none
SQL Update - multiple columns

    Question

  • Hi

     

    I'm having a problem updating multiple columns in a table.  Usually when I submit an update that affects multiple columns it's from an application using a stored procedure similar to the following:

     

    ALTER PROCEDURE [dbo].[AppointmentUpdate]

    (

    @appSubject nvarchar(160),

    @appDescriptionText text,

    @appType char(1),

    @appID int,

    )

     

    AS

    UPDATE dbo.tblAppointment

    SET

     

    appSubject = @appSubject,

    appDescriptionText = @appDescriptionText,

    appType = @appType,

     

    WHERE appID = @appID

     

    However, I have an existing stored procedure that I must modify and I would like to update multiple columns in a table based on values from a second table using a Select statement to obtain the values like this:

     

    UPDATE tbl1 SET (col1, col2, col3) = (SELECT colA, colB, colC FROM tbl2 WHERE tbl1.PK = tbl2.PK)

     

    However, it doesn't seem as though it's possible to 'SET' more than one column name - are there alternatives rather than writing separate update statements for each column?

     

    UPDATE tbl1 SET col1 = (SELECT colA FROM tbl2 WHERE tbl1.PK = tbl2.PK)

    UPDATE tbl1 SET col2 = (SELECT colB FROM tbl2 WHERE tbl1.PK = tbl2.PK)

    UPDATE tbl1 SET col3 = (SELECT colC FROM tbl2 WHERE tbl1.PK = tbl2.PK)

     

    Not sure if this makes sense but thanks for any replies
    Thursday, September 25, 2008 6:40 PM

Answers

All replies

  • You can do it in one update but the subquery can only produce 1 result:

     

    UPDATE tbl1

    SET col1 = (SELECT colA FROM tbl2 WHERE tbl1.PK = tbl2.PK),

    col2 = (SELECT colB FROM tbl2 WHERE tbl1.PK = tbl2.PK),

    col3 = (SELECT colC FROM tbl2 WHERE tbl1.PK = tbl2.PK)

     

     

    ...and you might want to consider a WHERE clause so every value in tbl1 is not updated.

     

    A.D.T.

    Thursday, September 25, 2008 6:45 PM
  •  

    Code Snippet

    UPDATE tbl1

    SET col1 =t2.colA, col2=t2.colB, col3=t2.colC

    FROM  tbl1 t1 INNER JOIN tbl2 t2 ON tbl1.PK = tbl2.PK

     

     

     

    Thursday, September 25, 2008 7:06 PM
  • Thanks to both for your replies.  limno - I think that's what I was looking for and will try it out when I have a chance...

    Thursday, September 25, 2008 9:19 PM
  • In addition to the above suggestions, here is the syntax I prefer to take confusion factor out of multi-table, multi-column UPDATE:

    UPDATE t1 
    SET    t1.col1 = t2.cola, 
           t1 .col2 = t2.colb, 
           t1 .col3 = t2.colc 
    FROM   tbl1 t1 
           INNER JOIN tbl2 t2 
                   ON t1.pk = t2.pk  

    Note that using table aliases, everything is defined precisely.

    UPDATE article: http://www.sqlusa.com/articles2005/sqlupdate/



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    Friday, September 26, 2008 12:45 AM
  • Hi -- this worked for me too.  Thanks!
    Bill Jones Director, ITIM Eastern Michigan University
    Thursday, July 21, 2011 6:17 PM
  • Hi -- this worked for me too.  Thanks!
    Bill Jones Director, ITIM Eastern Michigan University


    Hi Bill...

    Actually limno's answer in this thread is the better approach, since it does it with one (JOIN) query as opposed to three subqueries.  Here is what he posted:

    UPDATE tbl1
    SET col1 =t2.colA, col2=t2.colB, col3=t2.colC
    FROM  tbl1 t1 INNER JOIN tbl2 t2 ON tbl1.PK = tbl2.PK

    EDIT:  Sorry, SQLUSA's answer is "better" because he's more consistent in using the table alias:

    UPDATE t1
    SET col1 =t2.colA, col2=t2.colB, col3=t2.colC
    FROM  tbl1 t1 INNER JOIN tbl2 t2 ON tbl1.PK = tbl2.PK

     


    --Brad (My Blog)

     


    Thursday, July 21, 2011 6:23 PM