Update a column in a table with values from two other tables

Question

I have to update a column in Table 'A' with values from Table 'B'. If any value in Table 'B' is null or empty then I have to get the value from table 'C'.

Manu

Answer 1

Use:

UPDATE A
   SET column = (SELECT COALESCE(b.val, c.value)
                   FROM B b
                   JOIN C c ON c.col = b.col)

COALESCE will return the first non-null value from the list of columns, processing from left to right.

What's odd is you haven't provided how tables B and C relate to one another - if they don't in anyway, you're looking at a cartesian product of the two tables (not ideal). My answer uses a JOIN, in hopes it is possible depending on the data.

Answer 2

Basically:

UPDATE a SET a.FIELD = (CASE WHEN b.FIELD IS NULL or b.FIELD = '' THEN c.FIELD ELSE b.FIELD END)
FROM TABLEA a 
LEFT JOIN TABLEB b on a.id = b.someid
LEFT JOIN TABLEC c on a.id = c.someid

Joins may or may not be LEFT, depending on your data, and you may want to handle the case where both b.field and c.field are null.