Update null values column with values from the same column based on a query

Question

I have a table that has 2 columns, SSN and StateID. There are duplicate rows with the same SSN, but not all rows have a StateID. I want to select the rows that have a null StateID, and update it with the row with a matching SSN, and has a StateID.

Example of data

SSN           StateID 
123456789      XYZ 
123456789 
123456789
000000001      ABC 
000000001

I want to update the null values in StateID with the row value that does not have a null value.

Tag your question with the database that you are using.

Gordon Linoff
– Gordon Linoff

2019-09-09 01:12:24 +00:00
Commented Sep 9, 2019 at 1:12 — Gordon Linoff
– Gordon Linoff, Commented Sep 9, 2019 at 1:12

Gordon Linoff · Accepted Answer · 2019-09-09 01:13:35Z

1

First, you should fix your data model. It is a bad idea to replicate data across multiple rows. You should have a table with row per state and SSN and then use a join to connect them.

That said, in most databases you can do:

update t
    set stateid = (select max(t2.stateid) from t t2 where t2.ssn = t.ssn)
    where t.stateid is null;

answered Sep 9, 2019 at 1:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Update null values column with values from the same column based on a query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related