sql delete double value

Question

I have a Database with double data, now I wan't to delete the double data.

I can all ready filter the double value with this query:

SELECT model, count(model), max(product_id) as latest FROM sn1_product GROUP BY model HAVING COUNT(model) > 1

The output of the query is as followed:

How can I write a delete query which delete the latest id?

is `product_id` unique ? By context it looks like the same for each model. Do you have PK in this table? — triclosan, Feb 19 '15 at 11:13

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

What about something like:

DELETE FROM `sn1_product` 
WHERE product_id IN (
   SELECT  max(product_id) as latest FROM sn1_product 
   GROUP BY model HAVING COUNT(model) > 1
)

However, some databases (ie: MySQL) have problem deleting from the same table you are selecting. If you have this issue check the answer of this question

UPDATE: Workaround from the comments:

Create table (reference)

CREATE TABLE sn2_product AS
SELECT * FROM sn1_product;

Save only what you need (note the min function - info).... is the rest of the fields. Don't forget to copy over non-duplicates

INSERT INTO sn2_product
SELECT  min(product_id),... FROM sn1_product 
   GROUP BY model HAVING COUNT(model) > 1

INSERT INTO sn2_product
SELECT  * FROM sn1_product 
   GROUP BY model HAVING COUNT(model) = 1

Hope it helps

edited May 23 '17 at 12:21

Community

1
1

answered Feb 19 '15 at 11:17

urban

5,392
3
19
45

@triclosan: yes or the row id has to be in the select as the 1st value (I think) – urban Feb 19 '15 at 11:21
@urban the query doens't work the error i've got is: #1093 - You can't specify target table 'sn1_product' for update in FROM clause how can I fix this? – justkidding96 Feb 19 '15 at 11:48
@justkidding96: This is the issue mentioned by Rory Thoman and is solved in the post I linked to. I have no sql atm so I cannot help more... Also, a last workaround: create a clone of your table, select from the old and insert in the new one. Might be the fastest solution... – urban Feb 19 '15 at 12:09

score 0 · Answer 2 · answered Feb 19 '15 at 11:17

I can't really tell the purpose of this query so it is hard to give you a way to do what you are wanting but I will try and help what I can.

as far as I know you can't delete data from querying a table, can only delete data in the create table script.

the problem with filtering it this way is it still calculates and looks at the double data so takes extra time just to be removed anyway, instead you can use the term distinct which will only grab the first instance of each unique value.

also as an extra thought it should not be possible to create double data in one table, the primary key should be such that double data shouldn't be possible.

ps: I'm no expert this is just my thoughts so sorry if I am wrong and didn't help just trying to give some of my knowledge on.

sql delete double value

2 Answers2