johnslattery
12/30/2016 - 4:27 PM

SQLite doesn't support joins in update queries which makes for some complications in doing updates based on information in more than one tab

SQLite doesn't support joins in update queries which makes for some complications in doing updates based on information in more than one table. This example does not make use of row values introduced in v3.15.0 (2016-10-14).

sqlite-update-multi-table-pre-row-value.sql
content_copyfile_download
UPDATE cars
  SET

    /* We don't have row values introduced in version 3.15.0 (2016-10-14.) */
    model = (
      SELECT u.new_model
        FROM cars c natural join mod_bod_upd u
        WHERE cars.rowid = c.rowid),
    body = (
      SELECT u.new_body
        FROM cars c natural join mod_bod_upd u
        WHERE cars.rowid = c.rowid)

  /* Specify only rows that will have a value to set, otherwise model and body
     will be set to null. */
  WHERE cars.rowid IN (
    SELECT c.rowid
      FROM cars c natural join mod_bod_upd);