Re: [sqlite] help writing DELETE with JOIN
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 routine anyway, so all i needed to do was set them back to the old playlist ID, rather than delete them. here's my final, working statement: UPDATE playlist SET plID = 33 WHERE EXISTS ( SELECT 1 FROM song WHERE playlist.soID = song.soID AND song.plID = 33 AND song.Stal ) AND playlist.plID = 35 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help writing DELETE with JOIN
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 the playlist table's index is the plID, so i guess that's what i have to select to delete? is that what's wrong below? and here's the statement i came up with, it executes without error but does nothing to my tables: DELETE FROM playlist WHERE EXISTS ( SELECT 1 FROM song WHERE playlist.soID = song.soID AND song.plID = 33 AND song.Stal ) AND playlist.plID = 35 That statement when translated to English reads as follows (give or take some artistic license): Go through every record in the playlist, and whenever it is found that this record's plID is 35 and at the same time you can find a record in the song table which matches this playlist-record's soID, but also has a song.plID value which is exactly 33 and a boolean value in the song.Stal field which is 1, then delete this record from the playlist. Are you sure this is what you wish to happen? It seems unlikely to me that a song would have a plID that refers to an old playlist when it has already been added to a new playlist. When do you set the song's plID then? It seems to me this query is removing or preventing songs from belonging to a new playlist (35) when they already exist in an older playlist (33). Also, are you sure the .Stal field has boolean values? Does the Select query work as expected when joined with the playlist but without the delete statement? hmmm, oh SQLite, you so opaque! stop it! -- kJams: Mac and Windows Karaoke: CD+G Rip, Mix & Burn! Main: http://kjams.com/wiki/ Downloads: http://kjams.com/downloads/ What's New: http://kjams.com/history/ To Unsubscribe: Simply reply with "kJams: unsubscribe" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help writing DELETE with JOIN
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 guess that's what i have to select to delete? is that what's wrong below? and here's the statement i came up with, it executes without error but does nothing to my tables: DELETE FROM playlist WHERE EXISTS ( SELECT 1 FROM song WHERE playlist.soID = song.soID AND song.plID = 33 AND song.Stal ) AND playlist.plID = 35 hmmm, oh SQLite, you so opaque! stop it! -- kJams: Mac and Windows Karaoke: CD+G Rip, Mix & Burn! Main: http://kjams.com/wiki/ Downloads: http://kjams.com/downloads/ What's New: http://kjams.com/history/ To Unsubscribe: Simply reply with "kJams: unsubscribe" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help writing DELETE with JOIN
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 playlist.playlistID=songlist.playlistID AND playlist.playlistID=57 AND songlist.stale) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help writing DELETE with JOIN
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 "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 == X) and S.stale == true >> >> > (Composed in the mailer and untested:) > > DELETE FROM playlist > WHERE EXISTS(SELECT 1 FROM songlist > WHERE playlist.playlistID=songlist.playlistID > AND songlist.stale); > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help writing DELETE with JOIN
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 indices if the playlistID=57 constraint is on the WHERE clause of the UPDATE rather than the WHERE clause of the inner SELECT. > > also: what is the "1" for? > The result set of the SELECT inside of EXISTS(...) is ignored. Return whatever you like. I choose the constant 1. > > sorry for my newb-ness, still learning! but fun! > > DELETE FROM playlist > WHERE EXISTS(SELECT 1 FROM songlist > WHERE playlist.playlistID=songlist.playlistID >AND playlist.playlistID=57 >AND songlist.stale) > > > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help writing DELETE with JOIN
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 EXISTS(SELECT 1 FROM songlist WHERE playlist.playlistID=songlist.playlistID AND songlist.stale) AND playlistId=$x; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help writing DELETE with JOIN
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 playlistID X, i want to delete all rows from P who's > (P.playlistID == S.playlistID == X) and S.stale == true > > (Composed in the mailer and untested:) DELETE FROM playlist WHERE EXISTS(SELECT 1 FROM songlist WHERE playlist.playlistID=songlist.playlistID AND songlist.stale); -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] help writing DELETE with JOIN
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 == X) and S.stale == true aah! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
