CODEWITHSUNDEEP
sqlsqlite
William
William

Reputation: 1069

SQLITE: Update all records using concatenation but using the information from each row its on

Im having a bit of trouble. I am trying to update all records with the concatenation operator which allows me to create new strings and update specific fields with the new created string. Thing is, its working partially as its selecting the data from record 1 and applying that to all my records!

I need it to update each record using the data from that record not just from the first one.

Table layout:

trackpath|artistname|trackname

Example data i return:

location/rihanna - work.mp3|rihanna|work
location/rihanna - work.mp3|james blunt|beautiful

Expected data:

location/rihanna - work.mp3|rihanna|work
location/james blunt - beautiful.mp3|james blunt|beautiful

Update statement:

update table set trackpath = (SELECT 'location/' || artistname || ' - ' || trackname from table || '.mp3')

P.s, i tried where rowid = rowid in the select statement but same result!

Upvotes: 0

Views: 396

Answers (1)

CL.
CL.

Reputation: 180070

When you want the subquery to return different values for different rows, you must use a correlated subquery and specifiy how the data in the subquery relates to the data in the outer query:

UPDATE MyTable
SET x = (SELECT ...
         FROM MyTable AS T2
         WHERE T2.id = MyTable.id);

However, if you use only values from the same row, you do not need a subquery at all:

UPDATE MyTable
SET trackpath = 'location/' || artistname || ' - ' || trackname || '.mp3';

Upvotes: 2

Related Questions