Question: I'm facing some trouble when doing an update

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

1. If the **source table has null value** or **empty string** and **target has value**, it will **not update**
2. If the **source table has value** and **target value is null or empty string** it will **update**
3. 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!

Answer: 
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 <> '')

```
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
);

```