How to update the columns in table only when an if condition is satisfied?

I am trying to update a table field by looking up into another table. The lookup contains id field and its corresponding name field.

What I am trying to achieve is based on the match between the A table and B table I want to update name field in the A table with name (from the B) prefixed to the name field of the A table. So, if name in A table is ‘ABC’ and name in B is ‘XYZ’. I want XYZ_ABC in the name field of the A table.
The additional condition is if the lookup table name and main table name is both same like both fields have value ‘ABC’ then it should not append any prefix to it and the name should remain as it is.

Below is the code where I tried to achieve it although unable to add that condition. Can anyone help to achieve the same? TID is the ID, SNAME Is the name from table A and TNAME is the name from table B.

UPDATE TABLE_A
SET
    TABLE_A.SNAME = TNAME + '_' + a.SNAME
from TABLE_A a
left join TABLE_B b
    on a.TID = b.TID 
where b.TID in (
    select a.TID
    from TABLE_A a
    left join TABLE_C c on a.TID = c.TID
    where a.PHASE like '%SOMETEXT%'
)

Answer

Ignoring the structure of your where clause, the update you need to apply is simply to test for equality using a case expression

SET a.SNAME = case when a.SNAME=b.TNAME then a.SNAME else b.TNAME + '_' + a.SNAME end

Advertisement

Answer