SQLite update query - subquery with aliases does not work

Question

I need to update a SQLite table.

The table looks like this:

ID | Address | CallNumber | RefID -----+--------------------+------------------------------------------- ef78 | library | 2002/13 | 100002 no56 | Lit | 0189 | 100003 rs90 | temp | | 100003 

For each column with address = "Lit" there is a column Address = 'temp' with the same RefID. Now I need to update each address = "temp" with the value "CallNumber" from the column with the same RefID.

The updated table should look like this:

 ID | Address | CallNumber | RefID -----+--------------------+------------------------------------------- ef78 | library | 2002/13 | 100002 no56 | Lit | 0189 | 100003 rs90 | 0189 | | 100003 

I tried this:

 UPDATE Location SET address = foo.callnumber FROM (select RefID, CallNumber FROM Location) foo WHERE foo.RefID=Location.RefID AND Location.Address = 'temp'; 

But all I have is a syntax error next to the from.

Any clue?

Answer 1

UPDATE commands do not have a FROM .

Use the correlated subquery:

 UPDATE Location SET Address = (SELECT CallNumber FROM Location L2 WHERE L2.RefID = Location.RefID AND L2.Address = 'Lit') WHERE Address = 'temp'