skip to Main Content

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.

2

Answers


  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.

    Login or Signup to reply.
  2. 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