create delete statement for multiple rows and non unique columns

Question

Help me to write delete statement for sqlite db

'rating' table schema (rID, mID, rate, pub)

This query will return only one tuple

select * from rating
where rID = 101 and mID=678 and rate = 34.5 and pub='2012-11-03'

All columns in table are non unique

These queries do not work

delete from rating where rID,mID, rate, pub in 
    (select * from rating where rID = 101 and mID=678 and rate = 34.5 and pub='2012-11-03')

delete from rating where rID=G.rID,mID=G.mID, rate=G.rate, pub=G.pub
    (select * from rating where rID = 101 and mID=678 and rate = 34.5 and pub='2012-11-03') as G

Bohemian · Accepted Answer · 2013-02-10 11:49:45Z

1

Anything wrong with this?

delete from rating
where rID = 101 
and mID=678 
and rate = 34.5 
and pub='2012-11-03'

Any select statement can be converted into a delete statement that affects the same rows as those returned from the select. In fact, it's a good way to test what rows will be deleted.

answered Feb 10, 2013 at 11:49

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

2 Comments

simar Over a year ago

It is wrong becouse first statement 'select', second 'delete' and i need to make it in a single statement. lets make it complicated , for example i will modify query select * from rating where rID = 101 and mID=678 and rate = 34.5 and it will return 3 tuples with different pub. how to remove all of those tuples in single statement.

Bohemian Over a year ago

As I've said, any select can be converted to a delete; simply replace select * with delete and you will delete those same 3 tupples: delete from rating where rID = 101 and mID=678 and rate = 34.5. You seem to be resisting something very simple - what is your reservation?

Collectives™ on Stack Overflow

create delete statement for multiple rows and non unique columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related