CODEWITHSUNDEEP
postgresqlwhere-clausesql-update
Rohita Khatiwada
Rohita Khatiwada

Reputation: 2935

How to put where clause for multiple columns while updating multiple columns at the same time?

I want to update more than 10 columns at the same time , and my problem is I want to put where clause for all these columns.

My code is:

UPDATE Customer AS c
                    SET 
                        name = a.name,
                        address= a.address,
                        telephone = a.telephone,
                        --
                        --
                        --


        FROM Customer a 
                      INNER JOIN 
                        ( SELECT casenumber
                               , max(currentDate) AS md 
                          FROM Customer 
                          GROUP BY casenumber
                        ) AS z 
                      ON  z.casenumber = a.casenumber
                      AND z.md = a.currentDate
                    WHERE (a.casenumber = c.casenumber)

In the above statement I want to add condition as to update columns only when they are not 0.

for exmple, 

UPDATE Customer AS C
    SET name = a.name,
    address= a.address,
    ...

    ..
     WHERE a.name <> 0,
            a.address <> 0, 
            a.telephone <> 0
            ....
            ...

Is it possible to put where condition to check each column?

Any suggestions are appreciated..

Upvotes: 1

Views: 604

Answers (1)

user330315
user330315

Reputation:

Something like this (assuming name <> 0 is a typo and your names are really character columns)

 UPDATE customer AS c
     SET name = CASE WHEN name <> '' THEN a.name ELSE name END,
         address = CASE WHEN address <> '' THEN a.address ELSE address END

This essentially updates the column to it's current value if it's empty.

Note that this does not deal with NULL values! If you need to treat NULL and '' identically you need to use coalesce(name, '') instead.

Upvotes: 2

Related Questions