CODEWITHSUNDEEP
sqldatabasesqlite
simar
simar

Reputation: 1832

create delete statement for multiple rows and non unique columns

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

Upvotes: 1

Views: 100

Answers (1)

Bohemian
Bohemian

Reputation: 425238

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.

Upvotes: 1

Related Questions