Update multiple values from temporary table

Question

I'm very puzzled because when I'm running this query on SQLite.

On MacOS Mojave SQLITE, I'm getting a syntax error on the on "FROM". There is no more detail.

This does work on Postgres.

Am I reading the SQLite documentation the wrong way? https://sqlite.org/lang_update.html

Here's the query:

BEGIN;
-- Statement 1
CREATE TEMP TABLE tempEdits (identifier text, serverEditTime double precision);
-- Statement 2
INSERT INTO tempEdits (identifier, serverEditTime)
VALUES
    ('uuid1', 1.5),
    ('uuid2', 2.2),
    ('uuid3', 3.3);
-- Statement 3
UPDATE
    "pEdits"
SET
    "serverEditTime" = t.serverEditTime
FROM 
    "pEdits" AS e JOIN tempEdits AS t ON e.identifier = t.identifier
WHERE   
    e.identifier = t.identifier;
END;

Setup query:

CREATE TABLE "pEdits" (identifier text, serverEditTime double precision);
INSERT INTO (identifier)
VALUES
    ('uuid1'),
    ('uuid2'),
    ('uuid3');

Are you using the latest 3.33 release? That's when the new `UPDATE ... FROM` was added. Won't work on anything older. — Shawn, Aug 16 '20 at 09:33

Gordon Linoff · Answer 1 · 2020-08-16T02:47:13.483

3

The logic you want is:

UPDATE "pEdits"
    SET "serverEditTime" = t.serverEditTime
FROM tempEdits t 
WHERE "pEdits".identifier = t.identifier;

In other words, the table being updated should not be repeated in the FROM clause -- well, unless your intention is a self-join.

edited Aug 16 '20 at 02:47

answered Aug 15 '20 at 23:38

Gordon Linoff

1,242,037
58
646
786

This is what I first tried! Didn't work. Tried again—still doesn't work. All of these work on Postgres. My guess is there is some bug in the Sqlite on my version of MacOS...need to try somewhere else. Tried something like this too but SQLite seems to be crashing with this too :/ https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql#18799497 – ovatsug25 Aug 16 '20 at 02:02
As you wrote—`near "e": syntax error,` If I change the alias to `AS e` instead of just `e` then I get `near "FROM": syntax error.`—beats me! I was trying to find one syntax that would do it all in one SQL command instead of generating 200 UPATE statements. For SQLite I'm pretty sure it dosen't really matter if I do generate the 200 edit statements—but for my purposes I was really kind of hoping that the above would work b/c of my query builder. – ovatsug25 Aug 16 '20 at 02:37
@ovatsug25 . . . SQLite should do better in mimicking the Postgres functionality. – Gordon Linoff Aug 16 '20 at 02:47
I agree that this should work. On the flip side...I did discover something that would speed up the queries and be a nice API for both Sqlite and Postgres here . Plus it will save me from making a huge array and a huge string for my params :)— https://www.postgresql.org/docs/current/sql-prepare.html – ovatsug25 Aug 16 '20 at 03:50
This works fine on the current sqlite version (3.33), but will fail on older ones that lack `UPDATE ... FROM`. – Shawn Aug 16 '20 at 09:38
@ovatsug25 . . . I would have thought that you would be quoting the documentation from the version that you are actually using. Your question is rather misleading. – Gordon Linoff Aug 17 '20 at 01:15
@GordonLinoff - It wasn't intentionally misleading by any means. The SQLite docs are missing a helpful section in the docs where you can pick out the version number. Moreover—they don't actually mention the date the Update From was added which made me conclude it was always there. Do note the difference between https://sqlite.org/lang_update.html — In fact, it often comes at great annoyance to me because the Search Engine tends to bring up version 9 Postgres docs which I don't use. – ovatsug25 Aug 17 '20 at 14:10

score 3 · Accepted Answer · edited Aug 16 '20 at 12:13

3

SQLite does not support joins in the UPDATE statement.

Instead you should use a correlated subquery:

UPDATE pEdits
SET serverEditTime = (
  SELECT t.serverEditTime  
  FROM tempEdits AS t 
  WHERE t.identifier = pEdits.identifier 
);

See the demo.

Edit: Starting from version 3.33.0+ (2020-08-14), SQLite supports the Postgresql-like FROM clause. See https://www.sqlite.org/lang_update.html#upfrom

edited Aug 16 '20 at 12:13

ovatsug25

7,786
7
34
48

answered Aug 16 '20 at 07:38

forpas

160,666
10
38
76

1

As of the 3.33 release a few days ago, it does. Going to take a while for it to be commonly available, of course, though. – Shawn Aug 16 '20 at 09:40
Oh my! Ijust saw the 3.33 release on Hacker News this morning! So the universe and I had some kind of weird sync :) Will use it in the future but I can’t rely on it of course. Not sure whether Apple would let me have my own compile of SQLite in an older version of iOS. – ovatsug25 Aug 16 '20 at 12:07
@ovatsug25 as you can see in the comments, I did not know it either. The new version was released 2 days ago. Of course this particular change is good news, but I will have to find out what else is new. – forpas Aug 16 '20 at 12:10

Update multiple values from temporary table

2 Answers2