0

Is it possible to reference a column from a table if the column should be "extracted" from the case-statement?

update t1 
set col1 = value
from table1 t1, (select Col3, Col4 from table1) t2
where t1.(CASE 
             WHEN Col3 > 0 THEN Col1 
             ELSE Col2) = t2.(CASE WHEN Col3 > 0
                                   THEN Col1 
                                   ELSE Col2 
                              END)

Thanks in advance for help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bpesunny
  • 81
  • 1
  • 1
  • 6
  • Aside: [This](http://stackoverflow.com/a/1599201/92546) answer offers several reasons to consider using a more modern `JOIN` syntax. – HABO Jul 14 '16 at 10:56

1 Answers1

0

you can refer column name with table alias in a case statement like,

update t1 
set t1.col1 = value
from table1 t1, (select Col3, Col4 from table1) t2
where CASE 
         WHEN t1.Col3 > 0 THEN t1.Col1 
         ELSE t1.Col2 
        END = CASE WHEN t2.Col3 > 0
                THEN t2.Col1 
                ELSE t2.Col2 
            END

the case statements are evaluated first and then compared.

Jatin Patel
  • 2,066
  • 11
  • 13
  • This solution will not work for me, because I am also using a "group by" in t2 and cannot add some columns. Is there not another way of doing it? – bpesunny Jul 14 '16 at 09:48
  • you should put proper/full detail in question, so that you can get effective solution. using group by in derived query does not affect the outer condition! – Jatin Patel Jul 14 '16 at 10:37