CODEWITHSUNDEEP
mysqlsqlselectsql-delete
user1336101
user1336101

Reputation: 441

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.

Upvotes: 2

Views: 7249

Answers (1)

Deepak Sharma
Deepak Sharma

Reputation: 4170

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

Upvotes: 5

Related Questions