Mysql Question posted in
The official MySQL documentation can be found here.

MySQL change null values based on a value from another column

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search