News

Copyright © 2008-2019 Paula DiTallo

Tag Cloud



SQL Server: UPDATE Statement Basics

The last thing anyone wants to do is mess up an UPDATE statement! Here's a quick refresher for those of you using SQL Server:

Simplest case (a single row, in a single table with a single known value in a column):

UPDATE YourTableName
SET Column1 = ‘NewValue’
WHERE SameOrOtherColumn = ‘OldValue’

Most common case (multiple rows, using another table as the source data with multiple columns):

UPDATE YourTableName
SET ColumnX = OtherTable.Column1,
         ColumnY = OtherTable.Column2
FROM OtherTable
WHERE YourTableName.Column1 = OtherTable.Column1

Advanced case (multiple rows, multiple tables reliant on a subquery)

  UPDATE Target_Table
   SET col2 = z.colB,
       col3 = z.colC,
       col4 = z.colD,
       col5 = z.colE
     FROM
       (select x.col_a,
               x.col_b,
               y.col_b,
               y.col_b
            from tbl_1 x, tbl_2 y
           where x.col_c = y.col_c
        ) z
     WHERE Target_Table.col1 = z..colA
  

Think relational algebra for this implementation--basically, what you are doing in the subselect is gathering the data you need from the necessary tables, then redefining the results as 'z'. Once you've done that, you are assigning the target columns the values retrieved back from your subquery.

 

 

Monday, August 29, 2011 3:30 PM

Feedback

No comments posted yet.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: