CODEWITHSUNDEEP
mysqlsqldatabase
Phil Salesses
Phil Salesses

Reputation: 870

Move duplicate values to another column

I have a table with pairs of entries based on the column id_location.

"id_image",  "score",  "stddev", "id_image2", "score2", "stddev2", "id_location"
3484,        0.90422,  0.06476,  NULL,        NULL,     NULL,      13
18,          0.71598,  0.06101,  NULL,        NULL,     NULL,      13

If I wanted to merge duplicates for id_location and move them to a different column so each row is unique, how would I do that?

I looked at transpose and pivot but those seem slightly different.

This would be the resulting table:

"id_image",  "score",  "stddev", "id_image2", "score2", "stddev2", "id_location"
3484,        0.90422,  0.06476,  18,          0.71598,   0.06101,  13

Upvotes: 0

Views: 689

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

This would do it, with the higher id_image appearing in the first slot:

INSERT INTO new_table
SELECT t1.id_image, t1.score, t1.stddev, t2.id_image,
  t2.score, t2.stddev, t1.id_location
FROM old_table t1
JOIN old_table t2
ON t2.id_location = t1.id_location
  AND t2.id_image < t1.id_image

Upvotes: 2

berty
berty

Reputation: 2206

Maybe using subqueries :

UPDATE your_table
 SET
  score2 = (SELECT score FROM your_table WHERE id_image = 3484),
  stddev2 = (SELECT stddev FROM your_table WHERE id_image = 3484),
 WHERE id_image = 18;

Upvotes: 0

Related Questions