Menu
A programming error lead to multiple insertions of identical rows in this table. I am aware that constraints in the schema could prevent the insertions.
I am trying to find a way to delete all but the newest (highest id) row for each round/number pair. I could definitely script this but I wondered if there is a way to do this in pure SQL?
683
Follow these steps: Select the range of cells, or ensure that the active cell is in a table. On the Data tab, click Remove Duplicates . In the Remove Duplicates dialog box, unselect any columns where you don't want to remove duplicate values.
One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.
Generally you can do:
delete from your_table
where id not in
(
select max(id) from your_table
group by user, round, date, number
)
In MySQL you can't delete from the same table you are selecting from. But you can trick MySQL with another subquery like this:
delete from your_table
where id not in
(
select * from
(
select max(id) from your_table
group by user, round, date, number
) x
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
