0

I am trying to delete duplicate rows within mysql while keeping the latest one based on the Date and symbol columns. Here is my table (named daily) schema:

Date        High    Low     Open    Close   Volume  Adj_close   symbol   id
2021-08-18  48.770  48.160  48.560  48.220  5359300 47.350        mo    407593
2021-08-18  48.770  48.160  48.560  48.220  5359300 47.350        mo    407594
2021-08-19  48.310  47.710  47.820  48.240  4747800 47.370        mo    407595
2021-08-19  48.310  47.710  47.820  48.240  4747800 47.370        mo    407596
2021-08-20  48.690  48.010  48.250  48.470  3892800 47.600        mo    407597
2021-08-20  48.690  48.010  48.250  48.470  3892800 47.600        mo    407598

I tried using the below command, however I evidently "can't specify target table 'daily' for update in FROM clause.

DELETE FROM daily
where id not in
(
  SELECT      max(id)
  FROM        daily
  GROUP BY    `date`, symbol
)

Ultimately I want to do this from python, but understanding the sql is important going forward. Thanks in advance.

Seriously
  • 1
  • 1
  • Does this answer your question? [How to delete duplicates on a MySQL table?](https://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table) – Will B. Nov 08 '21 at 04:08

1 Answers1

0

Use JOIN

DELETE a
FROM daily a
JOIN (
  SELECT      max(id) id, `date`, symbol
  FROM        daily
  GROUP BY    `date`, symbol
) b ON a.`date` = b.`date` AND a.symbol = b.symbol AND a.id <> b.id

And even without finding the max(id)

DELETE a
FROM daily a
JOIN daily b ON a.`date` = b.`date` AND a.symbol = b.symbol AND a.id < b.id
ProDec
  • 5,390
  • 1
  • 3
  • 12