CODEWITHSUNDEEP
sqlduplicates
paultechguy
paultechguy

Reputation: 2518

SQL update rows to point to single key, then remove duplicates

I'm fixing an old database structure. Old table was storing an image (I've simplified this a lot):

Game

GameId, int
Name, varchar(50)
Picture, image

The table allowed rows with the same "Name" and that meant the same "Picture" existed...so we were wasting space by storing a duplicate image. We decided to move Picture/image into its own table.

Game

GameId, int
Name, varchar(50)
PictureId, int

Picture

PictureId, int
Picture, image

I have the SQL commands to do everything about moving the image column to the new table (and the image data itself), but I still have Game rows with the same "Name" pointing to their own Picture row (via Game.PictureId). I want all Game columns with the same "Name" pointing to a single Picture record.

What is the SQL command(s) to point all Game rows with the same "Name" to a single Picture row, then delete the duplicate Picture rows since no Game records use them any more?

Thanks.

Upvotes: 0

Views: 44

Answers (2)

Buddhi
Buddhi

Reputation: 421

Fiddle Link here: http://sqlfiddle.com/#!6/a2085/1

;WITH cte AS (
  SELECT name, max(pictureid) as pid
    FROM game
   GROUP BY name
)
UPDATE game 
   SET pictureid = b.pid
  FROM game a
       INNER JOIN
       cte b ON a.name = b.name;

delete from picture where pictureid not in (
select pictureid from game);

Upvotes: 2

Yossi Vainshtein
Yossi Vainshtein

Reputation: 3985

If it doesn't really matter to which of the duplicate images the Game rows will point, you can use something like

UPDATE Game g 
SET PictureId = 
  (SELECT MIN(g2.PictureId) from Game g2 where g2.Name = g.Name)

After this you can proceed to remove Picture rows without any games pointing them.

Upvotes: 0

Related Questions