Delete selected rows in mysql

Question

I would like to delete set of rows, which have same property like this SQL select.

SELECT item_id, count(*) as item_in_order
FROM items
GROUP BY order_id
HAVING item_in_order = 1

In words it means, that I have items of orders and I would like to delete rows, which are from orders with only one Item.

For example:

item_id    order_id
1          1
2          2
3          2
4          3
5          3
6          4

So I would like to delete rows with item_id 1 and 6.

nt clear.. tell little more.. you want to delete the record which u can select using this select query?? — Deepak Sharma, Jan 29 '14 at 14:32
Yes, exactly. I would like to delete set of rows form SELECT query that a post — user1336101, Jan 29 '14 at 14:35
This topic is silimar like http://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql , but query from this delete me each record. And I have got around 300k rows so query execution time was very long thanks to subslecet in subselect — user1336101, Jan 29 '14 at 14:39
I **fix it** with new temporary table with syntax: INSERT items_temp SELECT * FROM items GROUP BY order_id HAVING count(*) > 1 — user1336101, Jan 29 '14 at 15:13
Bad message, I do not fix it, because I "copy" grouped data in new table — user1336101, Jan 29 '14 at 15:28
can you please make it more clear.. didn't get ur last comment.. — Deepak Sharma, Jan 29 '14 at 15:47

score 5 · Accepted Answer · answered Jan 29 '14 at 14:48

5

you can use this query

delete from items
where order_id in 
   (SELECT order_id
    FROM items
    GROUP BY order_id
    HAVING count(*)  = 1
   )

it will delete the row where order_id exist only once in items table

answered Jan 29 '14 at 14:48

Deepak Sharma

2

Looks clear, I tried exactly this same but result is error: eror in query (1093): You can't specify target table 'items' for update in FROM clause – user1336101 Jan 29 '14 at 14:55
Yes I use. Is possible delete from same query that I select? I do not understant it. I think it is similar like this http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – user1336101 Jan 29 '14 at 15:04
hey dont mind but for time being u can try by duplicating this table and after deleting the record just delete the duplicate table.. does it make any sense?? – Deepak Sharma Jan 29 '14 at 15:12

1 Answers1