CODEWITHSUNDEEP
mysqlsql
raven
raven

Reputation: 123

Update field that have duplicates on another table

So I have a table of scores with a foreign key TeamID

ID     TeamID    Score   Month
1      1         100     1
2      2         90      2
3      2         80      3

And on table Team there are duplicates like this:

ID     TeamName
1      Team A
2      Team A

I want to update TeamID field on the score table to the first occurence on team table so I can safely delete duplicates on team table, for example to something like this:

ID     TeamID    Score   Month
1      1         100     1
2      1         90      2
3      1         80      3

any ideas how to do that without manually updating for each ID?

Upvotes: 0

Views: 35

Answers (3)

schlonzo
schlonzo

Reputation: 1404

This should work for you

UPDATE scores SET TeamID = (SELECT MIN(t3.ID) from Team t2 JOIN Team t3 ON t2.TeamName = t3.TeamName WHERE t2.ID = scores.TeamID)

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

select t1.id, t2.id as newId
from team t1 
     join (select min(id) as id, 
                 TeamName 
           from team
           group by TeamName) t2 on t1.TeamName=t2.TeamName

The query will return you which id should be replaced with new one. Use it in the score table to update ids

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

UPDATE score SET TeamID = (SELECT ID from Team ORDER BY ID limit 1);

Upvotes: 0

Related Questions