How to retain a row which is foreign key in another table and remove other duplicate rows?
- by Mithril
I have two table:
A:
id code
1 A1
2 A1
3 B1
4 B1
5 C1
6 C1
=====================
B:
id Aid
1 1
2 4
(B doesn't contain the Aid which link to code C1)
Let me explain the overall flow:
I want to make each row in table A have different code(by delete duplicate),and I want to retain the Aid which I can find in table B.If Aid which not be saved in table B,I retain the id bigger one.
so I can not just do something as below:
DELETE FROM A
WHERE id NOT IN (SELECT MAX(id)
FROM A
GROUP BY code,
)
I can get each duplicate_code_groups by below sql statement:
SELECT code
FROM A
GROUP BY code
HAVING COUNT(*) > 1
Is there some code in sql like
for (var ids in duplicate_code_groups){
for (var id in ids) {
if (id in B){
return id
}
}
return max(ids)
}
and put the return id into a idtable?? I just don't know how to write such code in sql.
then I can do
DELETE FROM A
WHERE id NOT IN idtable
