Sqlite Update Field If Null From Another Table

I have a sqlite3 DB with a different tables. What I'm doing is to UPDATE the last inserted row of a table with data from another record af the same table, or from another similar t

Solution 1:

You might consider this slightly more maintainable, at least it has some "triple-copied-code" less:

with shortcut(f1, f2, f3) as
 (SELECT fiedl1, fiedl2, fiedl3 FROM Table1 WHERE Table1_id =%d)
UPDATE Table1 SET 
  fiedl1 = ifnull(fiedl1, (SELECT f1 FROM shortcut)), 
  fiedl2 = ifnull(fiedl2, (SELECT f2 FROM shortcut)), 
  fiedl3 = ifnull(fiedl3, (SELECT f3 FROM shortcut))
WHERE Table1_id = (SELECTmax(Table1_id) FROM Table1)
;

Details:

• use a CTE for less repetition of conditions and other shortcutting
• use ifnull for making the conditional value simpler, shorter

Tested on SQLite 3.9.1, using a fixed value insteadof %d.