1

I'm moving an application from Oracle to SQLite as I don't need a persistent storage. The D/B is merely only used to sort a large number of records.

However the following SQL that works on Oracle fails on SQLite with syntax error.

Could anyone please point me what the error is?

       UPDATE atobas_files files
       SET (dlself_packs,dlself_recs,dl_packs,dl_recs,c4sm_packs,c4sm_recs,c4med_packs,c4med_recs,c4lg_packs,c4lg_recs) = (
           SELECT COALESCE( SUM(statestats.dlself_packs), 0 ),
                  COALESCE( SUM(statestats.dlself_recs),0 ),
                  COALESCE( SUM(statestats.dl_packs), 0 ),
                  COALESCE( SUM(statestats.dl_recs), 0 ),
                  COALESCE( SUM(statestats.c4sm_packs), 0 ),
                  COALESCE( SUM(statestats.c4sm_recs), 0 ),
                  COALESCE( SUM(statestats.c4med_packs), 0 ),
                  COALESCE( SUM(statestats.c4med_recs), 0 ),
                  COALESCE( SUM(statestats.c4lg_packs), 0 ),
                  COALESCE( SUM(statestats.c4lg_recs), 0 )
            FROM
                atobas_statestats statestats
                INNER JOIN atobas_files f2 ON statestats.fileid = f2.id
            WHERE f2.id = files.id
       )
       WHERE files.runid = 10;

Thanks!

est
  • 557
  • 1
  • 4
  • 17

1 Answers1

1

The type of update the shown statement is using (UPDATE table SET (col1, col1, ..., coln) SELECT ...) is not supported by all DBMS; only the standard one (UPDATE table SET col1=value1...) is supported in SQLite; but there's help, you can also update one table based on data from another table in SQLite.

Community
  • 1
  • 1
codeling
  • 11,056
  • 4
  • 42
  • 71
  • 1
    That syntax is actually not Oracle specific. It is ANSI SQL though not supported by all DBMS (DB2 allows it as well, and I think Teradata) –  Dec 14 '11 at 07:42
  • Thanks, have been working with SQL for a long time but haven'seen that syntax yet, only the (MS SQL-specific?) `UPDATE FROM`, which as far as I know is not in the standard. Very good to know that there is a standard conforming alternative! – codeling Dec 14 '11 at 08:09
  • So nyarlathotep's answer is correct that the syntax is not supported by SQLite? I'll flag it as the correct answer if so... – est Dec 15 '11 at 03:29
  • Take a look at the linked documentation, the syntax shown there suggests that the only accepted syntax is `UPDATE table SET ...`. – codeling Dec 30 '11 at 08:52