Update column values based on equal values in another column

user_18215824 0
Viewed 87

I'm using MySQL Workbench:

Starting point:

ParcelID Address
44 street xxxx
32 street yyyy
44 empty
44 null
12 empty
12 street zzzz

What should be:

ParcelID Address
44 street xxxx
32 street yyyy
44 street xxxx
44 street xxxx
12 street zzzz
12 street zzzz

Basically, I want to populate that same column of the same table if they have the same ParcelID. I read around that with a self join I could solve the problem, so far I've written this: any help?

UPDATE table t1 
INNER JOIN table t2  ON t1.ParcelID = t2.ParcelID
SET t1.Address = IF(t2.Address = '' OR NULL, t2.Address, t1.Address)
WHERE t1.ParcelID = t2.ParcelID;

any suggestions? Is writing the WHERE clause redundant?

Thanks in advance ^^

mysql-workbench sql-update mysql sql
1 Answers

One option could be moving the condition inside the join and set address2 to address1 directly, as done followingly:

UPDATE     tab t1
INNER JOIN tab t2
        ON t1.ParcelID = t2.ParcelID
       AND (t1.Address = '' OR t1.Address IS NULL)
       AND NOT (t2.Address = '' OR t2.Address IS NULL)
SET t1.Address = t2.Address;

Demo here.

user_124928900
Related