Updating a Row in Table Based on Column Conditions

Question

I have the below requirement to update gwtypelist column's NULL value with table_name column if it exists in gwtypelist

    table_name          typecode    gwtypelist           gwtypecode
cctl_banlength_ext  13to24months    cctl_banlength_ext  13to24months
cctl_banlength_ext  25to36months    cctl_banlength_ext  25to36months
cctl_banlength_ext  37to48months    cctl_banlength_ext  37to48months
cctl_banlength_ext  49to60months    cctl_banlength_ext  49to60months
cctl_banlength_ext  6to12months     cctl_banlength_ext  6to12months
cctl_banlength_ext  lessthan6months cctl_banlength_ext  lessthan6months
cctl_banlength_ext  morethan60months       NULL         NULL
cctl_country            UK             cctl_country     UK
cctl_country        Australia             NULL          NULL
cctl_vehicle        Audi                  NULL          NULL
cctl_vehicle        Masserati             NULL          NULL

Output: The third column's NULL value should be populated if it's exists in the first column

    table_name          typecode    gwtypelist           gwtypecode
cctl_banlength_ext  13to24months    cctl_banlength_ext  13to24months
cctl_banlength_ext  25to36months    cctl_banlength_ext  25to36months
cctl_banlength_ext  37to48months    cctl_banlength_ext  37to48months
cctl_banlength_ext  49to60months    cctl_banlength_ext  49to60months
cctl_banlength_ext  6to12months     cctl_banlength_ext  6to12months
cctl_banlength_ext  lessthan6months cctl_banlength_ext  lessthan6months
cctl_banlength_ext  morethan60months cctl_banlength_ext NULL
cctl_country            UK             cctl_country     UK
cctl_country        Australia          cctl_country     NULL
cctl_vehicle        Audi                  NULL          NULL
cctl_vehicle        Masserati             NULL          NULL

I tried running few update queries but I was always getting the same result.

update #comp
set gwtypelist = table_name
where gwtypelist in (select distinct table_name from #comp)

Please let me know if this can be done with the update statement or for any alternatives.

Tag your question only with the database you are using. The use of # in the table name suggests SQL Server. — Gordon Linoff
– Gordon Linoff, Commented Dec 3, 2017 at 15:43
Sure Gordon, but since it was related to just an update statement so I tagged other databases — Kishanu Bhattacharya
– Kishanu Bhattacharya, Commented Dec 3, 2017 at 15:46

Gordon Linoff · Accepted Answer · 2017-12-03 15:46:31Z

1

I think this does what you want:

update c
    set gwtypelist = c.table_name
    from #comp c
    where c.gwtypelist is null and
          exists (select 1 from #comp c2 where c2.gwtypelist = c.table_name);

answered Dec 3, 2017 at 15:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Updating a Row in Table Based on Column Conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related