Skip to main content
AI Assist is now on Stack Overflow. Start a chat to get instant answers from across the network. Sign up to save and share your chats.
edited body
Source Link
Lukas Eder
  • 223.5k
  • 138
  • 731
  • 1.6k

That depends on what you want to keep and what you want to remove. Since ID is a key, I'm guessing that there are no duplicate ID's but duplicate type/name pairs. So here's an idea on how to remove them:

delete from my_table t1 where exists (select 1 from my_table t2 where t2.type = t1.type and t2.name = t1.name and t2.id < t1.id) 

That will keep the "duplicate" with the lowest ID

 and t2.id <> t1.id 

That would keep the "duplicate" with the highest ID

That depends on what you want to keep and what you want to remove. Since ID is a key, I'm guessing that there are no duplicate ID's but duplicate type/name pairs. So here's an idea on how to remove them:

delete from my_table t1 where exists (select 1 from my_table t2 where t2.type = t1.type and t2.name = t1.name and t2.id < t1.id) 

That will keep the "duplicate" with the lowest ID

 and t2.id < t1.id 

That would keep the "duplicate" with the highest ID

That depends on what you want to keep and what you want to remove. Since ID is a key, I'm guessing that there are no duplicate ID's but duplicate type/name pairs. So here's an idea on how to remove them:

delete from my_table t1 where exists (select 1 from my_table t2 where t2.type = t1.type and t2.name = t1.name and t2.id < t1.id) 

That will keep the "duplicate" with the lowest ID

 and t2.id > t1.id 

That would keep the "duplicate" with the highest ID

Source Link
Lukas Eder
  • 223.5k
  • 138
  • 731
  • 1.6k

That depends on what you want to keep and what you want to remove. Since ID is a key, I'm guessing that there are no duplicate ID's but duplicate type/name pairs. So here's an idea on how to remove them:

delete from my_table t1 where exists (select 1 from my_table t2 where t2.type = t1.type and t2.name = t1.name and t2.id < t1.id) 

That will keep the "duplicate" with the lowest ID

 and t2.id < t1.id 

That would keep the "duplicate" with the highest ID