Deleting Playlist Entries via sql

[RigasW]

Hi,

I started using version 1586 on a slug with sqlite3 as database. I switched to that version, because it can be used together with the Java Standalone Client, that you can use to build playlists.

Unfortunately, the Java Client cannot remove songs from a playlist. As you create playlists with drag&drop, it is easy to add a song to the wrong playlist … (i am sure that the remove-feature will come some day…)

So I created the following SQL statement, that you have to run in sqlite3:

delete from playlistitems where playlistitems.id IN (select playlistitems.id from songs, playlistitems,playlists where songs.id=songid and playlists.id = playlistitems.playlistid AND songs.title like "%Summer%" AND playlists.title like "%oboe%");

In this example the Sql statement removes all entries containing “..Summer..” from a playlist with the name ” .. oboe ..”. In all other playlists the song with “summer …” is not removed.

I am not very familiar with SQL … may be one of the SQL pros here can have a look and do some adjustment, if necessary …

Rigas

[EVILRipper]

As you found yourself, Summer is deleted, but summer isn’t.
Case sensitivity is the problem.
A simple and common solution is casting both values to lowercase, then compare them:
… AND LOWER(songs.title) like “%summer%” AND …

[RigasW]

Hi,

I did some tests with SELECT statements like

select * from songs where Title like "%summer%";

It was not case sensitive … so the statement above found songs with “Summer ” in it, too.

Rigas

[Author]

Posts