Hello,

I'd like to make a remark to the revised UPDATE statement in v3.33 before it is finally released and cannot be changed anymore.

I have seen from the timeline that an extended syntax is being introduced:

UPDATE a SET ... FROM b WHERE ...

I appreciate this feature which has been missing far too long :)

However, the syntax seems to be PostgreSQL's and I see a problem there. The way PostgreSQL interprets the order of tables (especially not repeating the table name of the table actually being updated), imho the wording "FROM" is irritating. It should instead be called "JOIN" because that's what it actually is.

In MySQL, this feature spells:

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

This syntax is much more logical and I'd like to encourage the authors to accept this syntax as an alternative as well.

Just my 2 cts.

As far as I can tell, UPDATE FROM seems to be a branch which isn't merged in trunk and isn't included in version 3.33 of SQLite.

Yes, it is merged in the current trunk of version 3.33.0 of SQLite3.

You will notice that the current "public release" is version 3.32.3.

You will need to retrieve the "current trunk" from fossil and build the "current tip of trunk". The pre-release snapshot is not the current tip of trunk and does not have the UPDATE ... FROM ... changes yet.

Presumably once many of the bugs are out of it the pre-release snapshot will be updated, but you can always pull your own fossil clone and build the latest version yourself.

See the very end of the following pages for the links on how to clone the fossil repository for your own use and how to build SQLite3 from sources.

https://www.sqlite.org/download.html
https://www.sqlite.org/getthecode.html
https://www.sqlite.org/howtocompile.html

Uck.

That seems to have put the curse on my private branch which is updated from trunk.

It seems that undoing the merge of update-from into trunk did not return trunk (which had already been merged into my private branch). Hopefully it will fix itself when update-from is merged into trunk again. Or if there is another checkin to trunk.

At the moment it appears as a one-time merge from the update-from branch.

Well, it no fixed itself automatically so I ended up having to shun the merge after and redo it. Now it looks ok.

The UPDATE FROM idea is not standard SQL. And since there is no standard, everybody seems to implement it differently. SQLite chooses to follow PostgreSQL (hereafter "PG"), as it does with most contentious issues involving the SQL langauge. The other options are SQLServer and MySQL.

What is "UPDATE FROM"?

The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. The UPDATE-FROM idea is that you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows.

For example, suppose you have a point-of-sale application that accumulates purchases in the SALES table. At the end of the day, you want to adjust the INVENTORY table according to the daily sales. To do this, you can run an UPDATE against the INVENTORY table that adjusts the quantity by the aggregated sales for the day. In PG this query would do this job:

   UPDATE inventory
      SET quantity = quantity - daily.amt
     FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
    WHERE inventory.itemId = daily.itemId;

The equivalent SQL Server query is:

   UPDATE inventory
      SET quantity = quantity - daily.amt
     FROM inventory, 
          (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
    WHERE inventory.itemId = daily.itemId;

The SQL Server form of the query is the same as the PG form except that with SQL Server you repeat the target table in the FROM clause, but in PG you do not.

The MySQL form dispenses with the FROM clause all together and just puts all the tables to be joined where the target table is normally specified:

  UPDATE inventory JOIN
         (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
         USING( itemId )
     SET inventory.quantity = inventory.quantity - daily.amt;

The MySQL UPDATE statement does not have just one target table like the other systems. Any of the tables that participate in the join can be modified in the SET clause. The MySQL UPDATE syntax allows you to update multiple tables at once!

Why SQLite chooses the PG syntax

1. SQLite has traditionally followed PG syntax whenever practical.

2. The PG syntax is less wordy in that it avoids repeating the target table in the FROM clause.

3. The SQL Server approach does provide more control over the query that drives the UPDATE in that with SQL Server, the target table can be connected to other tables with various operators such as NATURAL JOIN or LEFT JOIN whereas in the PG approach, the target table is always just a comma-join using the WHERE clause. But while it does occasionally provide additional syntactic clarity, The SQL Server approach does not provide new capabilities.

4. The MySQL approach is more powerful in that it allows multiple tables to be updated at once. But the need to do that is rare. And modifying SQLite to support that feature would be a complete rewrite of the UPDATE logic and would require important structural changes to the parse tree. We won't know until we try, but such an effort seems likely to require months and would result in a system that is slower for common UPDATEs that only change a single table. We judged the added value of the MySQL approach to be not worth the amount of disruption that would result.

Note also that you can use triggers if you need more complicated selection of what to update. You can also use upsert with guaranteed conflicts (although this won't work with virtual tables; I would hope that this can be fixed in future). So, I think what SQLite is doing here is OK.