CODEWITHSUNDEEP
mysqljoinnullduplicatesfill
stochastiq
stochastiq

Reputation: 269

For duplicate records, set value mysql

I have a table in mysql that looks like this:

BusinessName ABN Status CountOfRecordsPerBusinessName
abccompany   123 Registered 2
abccompany   null Deregistered 2

Where business name is duplicated, and the ABN = null for Deregistered but not null for Registered status, I want to set the null ABN to 123.

How do I do this?

Upvotes: 0

Views: 86

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

We can try doing an update self join here:

UPDATE yourTable t1
INNER JOIN yourTable t2
    ON t1.BusinessName = t2.BusinessName AND
       t1.ABN IS NULL AND
       t2.ABN IS NOT NULL
SET t1.ABN = t2.ABN;

The basic idea here is to join a given business record where ABN is NULL to one from the same business where ABN is not NULL. Then, pull across the non NULL ABN value from the second to the first record. We don't have to worry about accidentally updating non duplicates, because the join condition would always fail for those.

Upvotes: 2

Related Questions