MySQL change null values based on a value from another column

t-Dawg 0
Viewed 54

I need some help with a query.

We have two columns

Id AccountId
201 a-123
NULL a-123
NULL a-123
202 a-234
203 a-345

How do I get the Id column to replace the NULL values based on if the AccountId column has the same value with the corresponding NULL value in the ID column?

Like this:

Id AccountId
201 a-123
201 a-123
201 a-123
202 a-234
203 a-345

I am not sure where to start, I have tried IF() and coalesce() but nothing is working.

SELECT Id, AccountId, IF(Id IS NULL, coalesce(Id, AccountId), Id)

Gives me this

Id AccountId
201 a-123
a-123 a-123
a-123 a-123
202 a-234
203 a-345

Any help is much appreciated.

mysql
2 Answers

You can write a subquery in a CASE statement to achieve this:

SELECT
  CASE WHEN Id IS NULL THEN 
          (SELECT Id 
           FROM sample_table 
           WHERE AccountId = a.AccountId
           LIMIT 1)
       ELSE Id
  END AS Id,
  AccountId
FROM sample_table a
ORDER BY Id ASC

However, a cleaner solution would be performing a CROSS JOIN and removing the records that are NULL during the join:

SELECT b.Id,
       a.AccountId
FROM sample_table a
CROSS JOIN sample_table b
  ON a.AccountId = b.AccountId AND b.Id IS NOT NULL
ORDER BY b.Id ASC

If you need to UPDATE your table, wrap the above query in a UPDATE statement:

UPDATE sample_table a
JOIN (SELECT c.Id,
             b.AccountId
      FROM sample_table b
          CROSS JOIN sample_table c
               ON b.AccountId = c.AccountId AND c.Id IS NOT NULL
      )  AS d ON a.AccountId=d.AccountId  
SET a.id = d.id

Result:

Id AccountId
201 a-123
201 a-123
201 a-123
202 a-234
203 a-345

db<>fiddle here.

GRIV0

Correct. e.g a-123 AccountId would never appear for multiple different Ids

A simple inner join update would do the trick:

update test t
inner join (select id,AccountId 
            from test 
            where id is not null 
           )  as t2 on t.AccountId=t2.AccountId  
set t.id=t2.id
where t.id is null

https://dbfiddle.uk/hoh4MYHu

Ergest Basha0
Related