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
– Deepak Sharma, Commented Jan 29, 2014 at 14:32
Yes, exactly. I would like to delete set of rows form SELECT query that a post — user1336101
– user1336101, Commented Jan 29, 2014 at 14:35
This topic is silimar like stackoverflow.com/questions/4562787/… , 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
– user1336101, Commented Jan 29, 2014 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
– user1336101, Commented Jan 29, 2014 at 15:13

Deepak Sharma · Accepted Answer · 2014-01-29 14:48:12Z

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, 2014 at 14:48

Deepak Sharma

4,1801 gold badge17 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1336101 Over a year ago

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 Over a year ago

Yes I use. Is possible delete from same query that I select? I do not understant it. I think it is similar like this stackoverflow.com/questions/45494/…

Deepak Sharma Over a year ago

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??

Collectives™ on Stack Overflow

Delete selected rows in mysql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related