CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alkippe Nikephoros
Alkippe Nikephoros

Reputation: 151

SQL Server - How to Update Multiple Columns/Conditions and Ignore Certain Columns in WHERE statement

I have two tables and would like to update table t in SQL Server 2008:

table w:

+--------+----+------+------+------+
| DataID | AFD|  PSD |  PFD | ASD  |    
+--------+----+------+------+------+
|      1 | B  | E    | A    |      |  
|      2 | I  |      | B    |  W   |      
|      3 |    |      | Y    |  D   |   
|      4 |    | Q    |      |  T   |     
|      5 | T  | E    | D    |  T   | 
+--------+----+------+------+------+

table t:

+--------+----+------+------+------+
| DataID | AFD|  PSD |  PFD | ASD  |    
+--------+----+------+------+------+
|      1 | A  | E    |      |  Z   |  
|      2 | B  | F    | Y    |      |      
|      3 | C  | G    | G    |  T   |   
|      4 | B  | E    |      |      |     
|      5 | T  | G    | Y    |  Z   | 
+--------+----+------+------+------+

Question/Attempt: How to update table t checking for multiple conditions?

Note: NULL is text not an empty column.

update t
SET t.ACTFINISHDATE = GETDATE()
,t.PROJSTARTDATE = 
(CASE 2
WHEN t.PROJSTARTDATE = 'NULL' THEN t.PROJSTARTDATE = w.PROJSTARTDATE
WHEN w.PROJSTARTDATE = 'NULL' THEN GETDATE()
ELSE GETDATE()
END)
,t.PROJFINISHDATE =
(CASE 3
WHEN t.PROJFINISHDATE = 'NULL' THEN t.PROJFINISHDATE = w.PROJFINISHDATE
WHEN w.PROJFINISHDATE = 'NULL' THEN GETDATE()
ELSE GETDATE()
END)
,t.ACTSTARTDATE =
(CASE 3
WHEN t.ACTSTARTDATE = 'NULL' THEN t.ACTSTARTDATE = w.ACTSTARTDATE
WHEN w.ACTSTARTDATE = 'NULL' THEN GETDATE()
ELSE GETDATE()
END)

FROM w
  JOIN t ON w.DataID = t.DataID
WHERE   ( ( cold IN ( 'A', 'B' ) 
       AND cols IN ( 'E', 'F' ) ) 
      OR ( cold = 'C' 
           AND cols = 'G' ) )

Question 2: How do I ignore update on C data in cold and F data in cols (so basically, ignore any data where Cold = C AND ColS = F)

table t with additional columns:

+--------+-----+-------+
| DataID | Cold|  ColS |    
+--------+-----+-------+
|      1 | A   | E     | 
|      2 | B   | G     |      
|      3 | A   | G     |  
|      4 | B   | E     |   
|      5 | C   | F     |
|      6 | C   | F     |
+--------+-----+-------+

Upvotes: 0

Views: 2057

Answers (2)

Turophile
Turophile

Reputation: 3405

Assuming the values are NULL and not 'NULL' as in the example, you can do this:

update t
SET t.ACTFINISHDATE  = GETDATE()
   ,t.PROJSTARTDATE  = COALESCE(t.PROJSTARTDATE,  w.PROJSTARTDATE,  GETDATE())
   ,t.PROJFINISHDATE = COALESCE(t.PROJFINISHDATE, w.PROJFINISHDATE, GETDATE())
   ,t.ACTSTARTDATE   = COALESCE(t.ACTSTARTDATE,   w.ACTSTARTDATE,   GETDATE())
 ....

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

Assuming your join is working, then your case statements should be changed as follows:

update t
set t.ACTFINISHDATE = GETDATE(),
    t.PROJSTARTDATE = 
        CASE WHEN t.PROJSTARTDATE = 'NULL' THEN w.PROJSTARTDATE
        ELSE GETDATE()
        END,
    t.PROJFINISHDATE =
        CASE WHEN t.PROJFINISHDATE = 'NULL' THEN w.PROJFINISHDATE
        ELSE GETDATE()
        END,
    t.ACTSTARTDATE =
        CASE WHEN t.ACTSTARTDATE = 'NULL' THEN w.ACTSTARTDATE
        ELSE GETDATE()
        END
FROM w
    LEFT JOIN T ON..
WHERE ....

You don't see many updates to outer joins though...

Upvotes: 1

Related Questions