Skip to main content

Return to Answer

Post Undeleted by FuzzyTree
added 50 characters in body
Source Link
FuzzyTree
FuzzyTree
  • 32.4k
  • 3
  • 58
  • 87

I think your conditionYou can be rewritten as the sum ofgroup by system_one_idleast and system_two_idgreatest should be uniqueto select the minimum id of each group and delete rows with other id's.

delete from mytable where id not in ( select * from ( select min(id) from mytable group by greatest(system_one_id, +system_two_id), least(system_one_id, system_two_id) ) t1 )

I think your condition can be rewritten as the sum of system_one_id and system_two_id should be unique.

delete from mytable where id not in ( select * from ( select min(id) from mytable group by (system_one_id + system_two_id) ) t1 )

You can group by least and greatest to select the minimum id of each group and delete rows with other id's.

delete from mytable where id not in ( select * from ( select min(id) from mytable group by greatest(system_one_id, system_two_id), least(system_one_id, system_two_id) ) t1 )
Post Deleted by FuzzyTree
deleted 72 characters in body
Source Link
FuzzyTree
FuzzyTree
  • 32.4k
  • 3
  • 58
  • 87

I think your condition can be rewritten as the sum of system_one_id and system_two_id should be unique.

delete from mytable where (id,(system_one_id + system_two_id)) not in ( select * from ( select min(id), (system_one_id + system_two_id) from mytable group by (system_one_id + system_two_id) ) t1 )

I think your condition can be rewritten as the sum of system_one_id and system_two_id should be unique.

delete from mytable where (id,(system_one_id + system_two_id)) not in ( select * from ( select min(id), (system_one_id + system_two_id) from mytable group by (system_one_id + system_two_id) ) t1 )

I think your condition can be rewritten as the sum of system_one_id and system_two_id should be unique.

delete from mytable where id not in ( select * from ( select min(id) from mytable group by (system_one_id + system_two_id) ) t1 )
Source Link
FuzzyTree
FuzzyTree
  • 32.4k
  • 3
  • 58
  • 87

I think your condition can be rewritten as the sum of system_one_id and system_two_id should be unique.

delete from mytable where (id,(system_one_id + system_two_id)) not in ( select * from ( select min(id), (system_one_id + system_two_id) from mytable group by (system_one_id + system_two_id) ) t1 )