CODEWITHSUNDEEP
sqlsql-servert-sql
no_event_logged
no_event_logged

Reputation: 59

Copying data from one row to another in the same column based on another column not being NULL

I have a issue with a table that has missing data from some rows, I need to copy the Artist column from another row that matches on title

Plays |  Title                   |  Artist
------+--------------------------+----------------
107   |  Superstition            |  Stevie Wonder
96    |  Superstition            |  NULL
158   |  Patience                |  Guns n Roses
9     |  Patience                |  NULL
112   |  Promised You A Miracle  |  Simple Minds
99    |  Promised You A Miracle  |  NULL
159   |  Baker Street            |  Gerry Rafferty
132   |  Baker Street            |  NULL

I have read a bunch of other questions on SO's that has landed me with this UPDATE statement:

UPDATE MetaDataTable
SET Artist = (SELECT TOP 1 Artist FROM MetaDataTable t 
              WHERE (Title = t.Title) AND t.Artist IS NOT NULL)
WHERE Artist IS NULL

What this end up with is the TOP 1 Artist being copied to all NULL Artist columns, not the Artist with the matching Title.

Plays |  Title                   |  Artist
------+--------------------------+----------------
107   |  Superstition            |  Stevie Wonder
96    |  Superstition            |  Stevie Wonder
158   |  Patience                |  Guns n Roses
9     |  Patience                |  Stevie Wonder
112   |  Promised You A Miracle  |  Simple Minds
99    |  Promised You A Miracle  |  Stevie Wonder
159   |  Baker Street            |  Gerry Rafferty
132   |  Baker Street            |  Stevie Wonder

What I would like is this:

Plays |  Title                   |  Artist
------+--------------------------+----------------
107   |  Superstition            |  Stevie Wonder
96    |  Superstition            |  Stevie Wonder
158   |  Patience                |  Guns n Roses
9     |  Patience                |  Guns n Roses
112   |  Promised You A Miracle  |  Simple Minds
99    |  Promised You A Miracle  |  Simple Minds
159   |  Baker Street            |  Gerry Rafferty
132   |  Baker Street            |  Gerry Rafferty

The rows with NULL Artist could be anywhere in the table and are not strictly below a complete row with the same Title.

Thanks!

Upvotes: 1

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would suggest an updatable CTE:

WITH toupdate as (
      SELECT mdt.*,
             MAX(mdt.artist) OVER (PARTITION BY mdt.title) as imputed_artist
      FROM MetaDataTable mdt
     )
UPDATE toupdate
   SET Artist = imputed_artist
WHERE Artist IS NULL;

Upvotes: 2

Dale K
Dale K

Reputation: 27225

You need to table qualify your title column since both tables are the same. Title = t.Title is the same as t.Title = t.Title because the closest matching column is the table in the sub-query, not the table being updated. Change it to MetaDataTable.Title = t.title.

UPDATE MetaDataTable
   SET Artist = (SELECT TOP 1 Artist FROM MetaDataTable t WHERE MetaDataTable.Title = t.Title AND t.Artist IS NOT NULL)
WHERE Artist IS NULL

Upvotes: 2

Related Questions