3

I need to delete some specific duplicates from my database. I've created a query that finds the records I need, but when running it as a subquery I get the error "operand should contain 1 column" I don't know how to get the specific information I need while only choosing a single column.

The query is : 

delete
from products
where id in (SELECT *
FROM products
GROUP BY part_number
 HAVING count(*) > 1 and manufacturer_id="4146"
)

Any idea how I can get this to work?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
nerdyone1977
  • 43
  • 1
  • 3
  • Replace `select * ` with `select id_column`. And please don't put numbers into double quotes. –  Feb 11 '14 at 19:36
  • Thanks for the tip. Unfortunately, this gives the response : Error Code: 1093. You can't specify target table 'products' for update in FROM clause – nerdyone1977 Feb 12 '14 at 23:26

1 Answers1

1

In your sub query you need to select id one column

delete
from products
where id IN (SELECT id
FROM products
WHERE manufacturer_id="4146" 
GROUP BY part_number
 HAVING count(*) > 1
)

Also getting id from same table will give you another error Can't specify target table you need to provide new alias to your subquery

delete
from products
where id IN 
(SELECT t.id FROM (
(SELECT id
FROM products
WHERE manufacturer_id="4146" 
GROUP BY part_number
 HAVING count(*) > 1 ) t
)
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • This gives the error: Error Code: 1054. Unknown column 'manufacturer_id' in 'having clause' – nerdyone1977 Feb 12 '14 at 23:24
  • Thanks for your help @M Khalid Junaid. The problem here is that the subquery pulls an empty set. There will not be any matching IDs in the products table. Maybe if I write it out in plain english what I am trying to accomplish. First I want to get all the parts that have a count more than 1 ... of those parts, I only want to delete those which have the manufacturer ID 4146. So I would write these two queries like : `Select part_number from products where count(part_number) > 1` and of that set `select part_number where manufacturer id='4146'` but how to bundle them into a delete query? – nerdyone1977 Feb 13 '14 at 20:11
  • @user116694 refer to this [thread](http://stackoverflow.com/questions/1043488/deleting-duplicate-rows-from-a-table?rq=1) – M Khalid Junaid Feb 14 '14 at 14:31