sqlite delete statement with two inner joins

Question

I've seen these two threads:
How delete table inner join with other table in Sqlite?
SQlite delete inner join
But im pretty sure this question isn't a duplicate.

So i wrote this select statement:

select * from tags
inner join pictures
    on pictures.id = tags.picture_id
inner join albums
    on pictures.album_id = albums.id
where tags.user_id = 1 and pictures.name = "Me and Obama" and albums.name = "Me and VIPs";

and now I need to pretty much make a delete statement out of it.
I tried replacing "select *" with "delete", but that's not correct syntax.

How would I go about doing this? This is what I have so far:

delete from tags
where picture_id in (select id from pictures where name = "Me and Moshe Dayan") and tags.user_id = 1

but it's missing the entire inner join with albums, which I have no idea how to implement.
Any help would be greatly appreciated!

Side note: SQL uses double quotes to mark identifiers like table and column names, and single quotes for strings. Sqlite will sometimes treat double quotes as string, if there's no identifier matching the contents, but it's still better to stick to the standard and use single quotes: `'Me and Obama'` etc. — Shawn, Mar 18 '19 at 19:22

score 1 · Accepted Answer · answered Mar 18 '19 at 18:23

I think you need the join in the subquery:

delete from tags
where picture_id in (select p.id
                     from pictures p inner join
                          albums a
                          on p.album_id = a.id
                     where p.name = 'Me and Moshe Dayan' and
                           a.name = 'Me and VIPs'
                    ) and
      tags.user_id = 1  ;

sqlite delete statement with two inner joins

1 Answers1