Re: [sqlite] help writing DELETE with JOIN

2013-11-18 Thread David Cotter
okay i figured it out. thank you all so much for your hints and suggestions!! my problem was that my "stal" marked items were the wrong items. once i marked the CORRECT items as stale, the statement worked. also i realized that the old playlist is about to be deleted by the caller of this

Re: [sqlite] help writing DELETE with JOIN

2013-11-16 Thread RSmith
On 2013/11/16 20:02, David M. Cotter wrote: okay i realize my requirements were wrong, here's a better summary: the plID (playlist ID) in the song table is different (the OLD id 33), the plID in the playlist table is the new ID 35, so i have to test them separately. the song ID's must match

Re: [sqlite] help writing DELETE with JOIN

2013-11-16 Thread David M. Cotter
okay i realize my requirements were wrong, here's a better summary: the plID (playlist ID) in the song table is different (the OLD id 33), the plID in the playlist table is the new ID 35, so i have to test them separately. the song ID's must match the playlist table's index is the plID, so i

Re: [sqlite] help writing DELETE with JOIN

2013-11-16 Thread David Cotter
outside of the parens? shouldn't it go inside the parens? eg: say the playlist ID i want is "57", would i do this? also: what is the "1" for? sorry for my newb-ness, still learning! but fun! DELETE FROM playlist WHERE EXISTS(SELECT 1 FROM songlist WHERE

Re: [sqlite] help writing DELETE with JOIN

2013-11-16 Thread David Cotter
what about "playlistID=X" ? the playlist table has "playlistID", (different playlists) i only want the ones in a particular playlist On Nov 15, 2013, at 5:36 AM, Richard Hipp wrote: > On Fri, Nov 15, 2013 at 2:55 AM, David M. Cotter wrote: > >> i have a

Re: [sqlite] help writing DELETE with JOIN

2013-11-15 Thread Richard Hipp
On Fri, Nov 15, 2013 at 11:44 AM, David Cotter wrote: > outside of the parens? > > shouldn't it go inside the parens? > eg: say the playlist ID i want is "57", would i do this? > I think it gets the same result either way, right? But it seems more likely to use available

Re: [sqlite] help writing DELETE with JOIN

2013-11-15 Thread Richard Hipp
On Fri, Nov 15, 2013 at 11:01 AM, David Cotter wrote: > what about "playlistID=X" ? > > the playlist table has "playlistID", (different playlists) > i only want the ones in a particular playlist > > So add "AND playlistID=$x" to the WHERE clause. DELETE FROM playlist WHERE

Re: [sqlite] help writing DELETE with JOIN

2013-11-15 Thread Richard Hipp
On Fri, Nov 15, 2013 at 2:55 AM, David M. Cotter wrote: > i have a "song" table S that has "songID", "playlistID", and "stale" > (boolean) as columns (among others) > i have a "playlist" table P that has "playlistID" and "songID" as columns > (among others) > > for a particular

[sqlite] help writing DELETE with JOIN

2013-11-15 Thread David M. Cotter
i have a "song" table S that has "songID", "playlistID", and "stale" (boolean) as columns (among others) i have a "playlist" table P that has "playlistID" and "songID" as columns (among others) for a particular playlistID X, i want to delete all rows from P who's (P.playlistID == S.playlistID