CODEWITHSUNDEEP
sqlsql-servert-sql
Ice
Ice

Reputation: 459

Update and self join

I want to update the blank name column in client table using the non-blank column with same client number. Do you know how I should modify the below SQL? Thanks,

UPDATE CLIENT
    SET NAME=(SELECT NAME FROM CLIENT WHERE (NAME IS NOT NULL AND NAME<>'') )
FROM CLIENT C1, CLIENT C2
WHERE (NAME IS NULL OR NAME='') AND C1.CLIENT_NO=C2.CLIENT_NO

Upvotes: 2

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would recommend using window functions for this purpose:

with toupdate as (
      select c.*, max(name) over (partition by client_no) as maxname
      from client c
     )
update toupdate
    set name = maxname
    where name is null or name = '';

Upvotes: 1

Related Questions