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
