SQL - Update when source column is not blank-CodePudding

I'm facing some trouble when doing an update

Now I'm trying to do update with condition below:

If the source table has null value or empty string and target has value, it will not update
If the source table has value and target value is null or empty string it will update
If both has value then it will update the target value from source value

I have tried using OR in WHERE clause but it still update when the source is empty string Here is my code

UPDATE a
SET a.column1 = b.column1
    a.column2 = b.column2
    a.column3 = b.column3
    .....
from TARGET t
inner join SOURCE s
on t.name = s.name
and t.gender = s.gender
where (
    s.column1 <> ''
    s.column2 <> ''
    s.column3 <> ''
    ........
)

I have an idea to use CASE WHEN on every column but I think that's my last option.

Thanks!

CodePudding user response：

Condition 1 : use NULLIF() to check for empty string and then COALESCE() to check for column1, if it is null, set it back to t.column1.

UPDATE t
SET   column1 = COALESCE(NULLIF(s.column1, ''), t.column1)

Condition 2 : check in WHERE clause

WHERE s.column1 IS NOT NULL 
AND   s.column1 <> ''

Condition 3 is actually already handled by 1 & 2.

And to handle checking multiple columns, use OR on the WHERE clause

WHERE (s.column1 IS NOT NULL AND   s.column1 <> '')
OR    (s.column2 IS NOT NULL AND   s.column2 <> '')
OR    (s.column3 IS NOT NULL AND   s.column3 <> '')

CodePudding user response：

As on column may be empty when another contains a value and can be updated you can use a CASE statement like.
If you only want to update if all values a empty or null you need to put all the conditions in the where.

UPDATE a
SET a.column1 = CASE WHEN COALESCE(a.column1, '') = '' THEN b.column1 ELSE a.column1 END
    a.column2 = CASE WHEN COALESCE(a.column2, '') = '' THEN b.column2 ELSE a.column2 END
    a.column3 = CASE WHEN COALESCE(a.column3, '') = '' THEN b.column3 ELSE a.column3 END
from TARGET t
inner join SOURCE s
on t.name = s.name
and t.gender = s.gender;

with all WHERE conditions

UPDATE a
SET a.column1 = b.column1
    a.column2 = b.column2
    a.column3 = b.column3
    .....
from TARGET t
inner join SOURCE s
on t.name = s.name
and t.gender = s.gender
where (
    (a.column1 = '' OR a.culumn1 IS NULL)
AND (a.column2 = '' OR a.culumn2 IS NULL)
AND (a.column3 = '' OR a.culumn3 IS NULL)
AND b.column1 IS NOT NULL
AND b.column2 IS NOT NULL
AND b.column3 IS NOT NULL
);