Delete row and referencing rows in another table in SQLite 3

Question

I'm using SQLite to play around and learn some more SQL. I have a SQLite 3 database populated like this:

create table playlist (id integer primary key autoincrement, name text);

create table playlistitem (id integer primary key autoincrement, 
    playlist_id integer, name text);

insert into playlist (name) values ("Moss");
insert into playlist (name) values ("Jen");

insert into playlistitem (playlist_id, name) values (1, "Roy");
insert into playlistitem (playlist_id, name) values (1, "Richmond");
insert into playlistitem (playlist_id, name) values (2, "Denholm");

Great, now I have two playlist items in the "Moss" playlist, "Roy" and "Richmond"; I have one item in the "Jen" playlist: "Denholm".

What I'd like to do is delete the "Moss" playlist and all of its items with a single query.

I saw something like this, which fails for me:

delete playlist, playlistitem from playlist
    inner join playlistitem on playlistitem.playlist_id = playlist.id
    where playlist.name = "Moss";

Failure:

Error: near "playlist": syntax error

What am I doing wrong?

keltar · Accepted Answer · 2015-01-30 18:27:21Z

2

sqlite doesn't support join in delete statement. You have to use separate query that deletes from second table based on playlist_id, making a delete trigger on playlist, or make that reference a foreign key with on delete cascade:

create table playlistitem (
    id integer primary key autoincrement,
    playlist_id integer, name text,
    foreign key(playlist_id) references playlist(id) on delete cascade);

and then just using delete from playlist where name='Moss'.

Don't forget to enable foreign keys - pragma foreign_keys=1 (you have to re-enable this on each sqlite connection, e.g. as the first command after connecting).

answered Jan 30, 2015 at 18:27

keltar

18.5k3 gold badges40 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Delete row and referencing rows in another table in SQLite 3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related