CODEWITHSUNDEEP
sqlsql-servernullduplicates
Fear605
Fear605

Reputation: 35

Removing dups and updating null values

I've just been tasked with removing all the duplicate values in a database. Simple enough. But they also want me to go through and check if there are any Null values that were not Null in previous entries for that record.

So let's say that we have user 123. User 123 doesn't have a zip code listed for whatever reason. But in a past entry he had zip code 55555. I'm supposed to update the latest entry with that zip code from a past entry and then delete the past entry. Leaving me with only one entry for user 123 AND having the zip code 55555.

I'm just unsure how to do the update portion. Anybody have any suggestions?
Thanks!

Upvotes: 1

Views: 130

Answers (2)

MicSim
MicSim

Reputation: 26826

You could use a statement similar to the following one:

update t1
set t1.address = dt.address,
    t1.city = dt.city,
    ... and so on ...
from your_table as t1
inner join 
(
select 
    max(id) as id,
    companyname,
    max(address) as address,
    max(city) as city,
    ... and so on ...
    from your_table
    group by companyname -- your duplicate detection goes here
) dt
on dt.id = t1.id

This way you fill up all gaps in your duplicates. Then you just have to delete the duplicates.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Here is how you can do the update. It finds the last value for zip, and then updates the field, if necessary:

with lastval as (
    select *
    from (select id, zip, row_number() over (partition by id order by datecreated desc) as seqnum
          from t
          where zip is not null
         ) t
    where seqnum = 1
)
update t
    set t.zip = lastval.zip
    from lastval
    where t.id = lastval.id

However, I would suggest that you create a new table with the data that you want. Don't both deleting and updating a zilion rows, create a table using a query such as:

select *
from (select t.*, row_number() over (partition by id order by datecreated desc) as seqnum
      from t
      where zip is not null
     ) t
where seqnum = 1

And insert the rows into a new table.

And, one more suggestion. Ask another question, with a better notion of what the fields are like in the table, and which ones you want to look up last values for. That will provide additional information for better solutions.

Upvotes: 2

Related Questions