Adding text to matching values, linking them to songbook. [SQL]

Azaziah

Hello! I have the following scenario: 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: 31, 32, 100, 101 etc.. (Value is stored on table: ”songs” field: ”title”) 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. So if I have song titled as ”25” I want to rename it to ”HL 025” and add it to songbook with number ”25” (Further details of database: 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” should be linked to ”id” with  ”2”. ) Would it be possible to do the changes with code, so we wouldn’t need to manually rename/add the songs to book? Blessings and thanks in advance!

raoul

Yes, you just need to write a script to run the SQL and update the data. It's probably a good idea close OpenLP while you're doing this.

tgc

Hi Azaziah,

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;

Azaziah

Thank you very much for replies! I opened a new discussion on here: https://ask.sqlservercentral.com/questions/116315/adding-text-to-matching-values-linking-them-to-son.html

tomasss

Hello, Do you mean this? update songs set title='HL '||title,song_number=title,song_book_id=2 where title like '___'; tomas

Azaziah

That worked like magic, our nightmare DB is a lot better now! Thanks! :)