Conditional CASE statement inside an INSERT TO statement

Database
Enthusiast

Conditional CASE statement inside an INSERT TO statement

Hi guys,

How can I achieve something like this...

insert into DB.table (NEWorEMPTYCOL) values (

  case

    when certain_attribute_in_table like 'val' then 'val_in_new_col'

    ...

    end

Tags (1)
1 REPLY
Enthusiast

Re: Conditional CASE statement inside an INSERT TO statement

create table db_name.test(col1 int, col2 char(1))

insert into db_name.test values(2,'a');
insert into db_name.test values(3,'a');
insert into db_name.test values(4,'b');
Alter table db_name.test add  col3 int;

create table db_name.test1 as db_name.test with no data

insert into db_name.test1 
sel col1,col2,case when col1=2 then 1 else col1 end col3 from db_name.test;

Alternate to the above you can use the below update to get the 3rd new column updated

update db_name.test   b
set col3=case when b.col1=2 then 1 else b.col1 end