jgordin
asked on
one sql update statement
i have to tables: table1 and table2. i need to update a column in table1 with either 1 or 0 based on column in table2. that is, if table2.column = C then table1.column = 0
if table2.column = P then table1.column = 1
how do i write one sql update statement that would do it?
if table2.column = P then table1.column = 1
how do i write one sql update statement that would do it?
ASKER
thanks. what would be the syntax if i need to set more than two fields:
i tried to add column2 = CASE when t2.column = 'E' then 0 when t2.column = 'A' then 1 else null end
but it doesnt work.
update table1
set column = CASE when t2.column = 'C' then 0 when t2.column = 'P' then 1 else null end
column2 = CASE when t2.column = 'E' then 0 when t2.column = 'A' then 1 else null end
from table1 t1
join table2 t2
on t2.key = t1.key
i tried to add column2 = CASE when t2.column = 'E' then 0 when t2.column = 'A' then 1 else null end
but it doesnt work.
update table1
set column = CASE when t2.column = 'C' then 0 when t2.column = 'P' then 1 else null end
column2 = CASE when t2.column = 'E' then 0 when t2.column = 'A' then 1 else null end
from table1 t1
join table2 t2
on t2.key = t1.key
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
set column = CASE when t2.column = 'C' then 0 when t2.column = 'P' then 1 else null end
from table1 t1
join table2 t2
on t2.key = t1.key