MySQL change null values based on a value from another column

Question

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.

I want my result to have those null values be 201 since the corresponding AccountId is the same — t-Dawg
– t-Dawg, Commented Sep 14, 2022 at 18:28
Do not add crucial context to the comments. Edit your question and include it there. Also, what have you tried so far? Please include a minimal, reproducible example. "It doesn't work" is not a clear or valid explanation of behavior, especially because we have no idea what "it" is. — Jesse
– Jesse, Commented Sep 14, 2022 at 18:30
With my dataset the AccountId only show if there is an existing Id — t-Dawg
– t-Dawg, Commented Sep 14, 2022 at 18:46
What if the third a-123 was something else, like a-345, would the second NULL also become 201, or would it stay NULL? Your example is ambiguous. You're gonna need @foo vars either way, and that's no fun IMO. — Rudie
– Rudie, Commented Sep 14, 2022 at 18:47
Correct. e.g a-123 AccountId would never appear for multiple different Ids — t-Dawg
– t-Dawg, Commented Sep 14, 2022 at 18:55

griv · Accepted Answer · 2022-09-14 20:05:35Z

1

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.

edited Sep 14, 2022 at 20:05

answered Sep 14, 2022 at 18:56

griv

2,2852 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

t-Dawg Over a year ago

Thank you so much!! I used the CROSS JOIN method as it worker better.

Ergest Basha · Accepted Answer · 2022-09-14 19:55:38Z

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

answered Sep 14, 2022 at 19:55

Ergest Basha

9,3164 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL change null values based on a value from another column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related