SQL Update each row conditionally

Question

I have run into a complex situation. I searched all the blogs but could not find any answers close to my expectation. I am running the following query :

**Table1                                                        Table2**    
**ID    Status  RptGChng    RptAChng    RptRChng        |    ID    Status**
8614    Green    0             0        0               |    8614   Red
8548    Unknown  0             0        0               |    8548   Amber
2591    Amber    0             0        0               |    2591   Amber
7813    Green    0             0        0               |    7813   Green
8413    Red      0             0        0               |    8413   Red
8183    Green    0             0        0               |    8183   Green
7431    Red      0             0        0               |    7431   Red
7399    Green    0             0        0               |    7399   Red
7776    Unknown  0             0        0               |    7776   Unknown
8609    Green    0             0        0               |    8609   Green
8068    Green    0             0        0               |    8068   Green

The RptGChng should give me a value of 1 if there has been a change in Green value of the id from Table1 to Table2. I need to populate in this manner for RptAChng -> Amber and RptRChng -> Red. The code that I wrote to arrive at this was as follows:

 set [RptGChng]   =
 IIF(([Status]='Green'),1,0)-
 IIF(([Status]=(
 SELECT b.[Status] FROM [Table1] a INNER JOIN [Table2] b  ON
 a.[id]= b.[id] and b.[Status]='Green')),1,0)

M.Ali · Accepted Answer · 2014-12-27 18:33:47Z

2

UPDATE t1
 SET t1.RptGChng = CASE WHEN t1.[Status] = 'Green' AND  t1.[Status] <> t2.[Status] 
                        THEN 1 ELSE t1.RptGChng END 
    ,t1.RptAChng = CASE WHEN t1.[Status] = 'Amber' AND  t1.[Status] <> t2.[Status] 
                        THEN 1 ELSE t1.RptAChng  END 
    ,t1.RptRChng = CASE WHEN t1.[Status] = 'RED'   AND  t1.[Status] <> t2.[Status] 
                        THEN 1 ELSE t1.RptRChng END 
FROM Table1 t1 
INNER JOIN Table2 t2  ON t1.[id]= t2.[id]

Since you have used IIF Expression I think you are using SQL Server 2012 or later, you can write the above statement with IIF too:

UPDATE t1
 SET t1.RptGChng = IIF(t1.[Status] = 'Green' AND  t1.[Status] <> t2.[Status], 1 , t1.RptGChng) 
    ,t1.RptAChng = IIF(t1.[Status] = 'Amber' AND  t1.[Status] <> t2.[Status], 1 , t1.RptAChng)
    ,t1.RptRChng = IIF(t1.[Status] = 'RED'   AND  t1.[Status] <> t2.[Status], 1 , t1.RptRChng)
FROM Table1 t1 
INNER JOIN Table2 t2  ON t1.[id]= t2.[id]

answered Dec 27, 2014 at 18:33

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

I'm impressed at your ability to understand the question.

SKP Over a year ago

I used the 2nd code that @M.Ali wrote but I got the multi-part identifier column could not be bound. I had to make a small tweak and it fixed. UPDATE t1 SET RptGChng = IIF(t1.[Status] = 'Green' AND t1.[Status] <> t2.[Status], 1 , t1.RptGChng) ,RptAChng = IIF(t1.[Status] = 'Amber' AND t1.[Status] <> t2.[Status], 1 , t1.RptAChng) ,RptRChng = IIF(t1.[Status] = 'RED' AND t1.[Status] <> t2.[Status], 1 , t1.RptRChng) FROM Table1 t1 INNER JOIN Table2 t2 ON t1.[id]= t2.[id]

Collectives™ on Stack Overflow

SQL Update each row conditionally

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related