Search code examples
mysqlsqlselectsql-delete

Delete selected rows in mysql

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.

Solution

  • 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