Adding text to matching values, linking them to songbook.
Hello! I have the folowing scenario: We use OpenLP program for showing song lyrics, songs come from .sqlite database. In addition to other songs, our database has some 600 songs that belong to songbook called ”Hengellinen laulukirja” Most of them are named plainly as: 031, 032, 100, 101 etc. Some of them are named ”HL 001”, ”HL 461” etc. This is the format in which I want all of the songs from the book to be renamed to. (HL stands short for ”Hengellinen laulukirja”) I also want to add songs to songbook with corresponding numbers. The database has related tables ”songs” and ”song_books” ”songs” table has fields called: ”title” for song name, ”song_book_id” and ”song_number” (which is the songbook number) ” song_books” has ”id” field with value ”2” on the desired songbook. (So the ”song_book_id” on ”songs” should be linked to ”id” with ”2” on ”song_books”. So if I have song titled as ”025” I want to rename it to ”HL 025” and add it to songbook ”2” with number ”25” On OpenLP support I got the following hint: ”You can probably use this as a starting point, it finds songs where the title is a number and displays the number in a 4 digit format: select printf("HL %04d",cast(title as integer)) from songs where cast(title as interger) != 0; ” Heres a download link for the database file:
https://drive.google.com/file/d/0B0HrW30NoImpR0F5RzU3Snh0WFU/view?usp=sharing I am not that good with SQL myself, but perhaps I can find the solution here. I wish to give many thanks to anybody reading and wishing to help.
I got solution on OpenLP forums: update songs set title='HL '||title,song_number=title,song_book_id=2 where title like '___'; Yeah, I got misled by the SQL term, sorry! :D I'm a little bit wiser now. Thanks! :)
You have come to a wrong forum as this forum is dedicated to SQL Server RDBMS and your question refers to SQLLite RDBMS. perhaps the word "SQL" landed you here. :D I am not SQLLite expert but just hope someone here with that expertise may answer your question.