All Forums Database
jhilliar 7 posts Joined 07/14
11 Jul 2014
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

krishaneesh 140 posts Joined 04/13
11 Jul 2014
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

 

You must sign in to leave a comment.