SQLite / MySQL compatibility issue

Question

I know SQL in SQLite is not completely implemented the same way as in MySql. My problem with the following queries is, that they are not compatible and I like to avoid a conditional if <DBMS> ... else

SQLite query

UPDATE sorties SET state = '#' 
WHERE `key` IN (
  SELECT `key` FROM sorties 
  INNER JOIN reports AS r 
    ON r.sortieId=sorties.`key`);

Error on MySQL:

SQL Error (1093): Table 'sorties' is specified twice, both as a target for 'UPDATE' and as a separate source for data

MySQL query (adapted from here)

UPDATE sorties AS s SET s.state='#'
WHERE s.`key` IN ( 
  SELECT t.sortieId FROM (
    SELECT r.sortieId AS sortieId
    FROM reports AS r
      INNER JOIN sorties AS sort
        ON sort.`key`=r.sortieId) 
    AS t);

Error on SQLite:

SQLiteManager: Likely SQL syntax error: UPDATE sorties AS s SET s.state='#' WHERE s.key IN ( SELECT t.sortieId FROM ( SELECT DISTINCT r.sortieId AS sortieId FROM reports AS r INNER JOIN sorties AS sort ON sort.key=r.sortieId) AS t); [ near "AS": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

I can't figure out how to make this queries work on both systems equally!

All I want to have is, that each state of sorties must be '#' when it's key can be found in reports.sortieId.

Maybe there is a different approach for this?

Thank you

CL. CL. · Accepted Answer · 2017-05-22T16:32:28

The first command reads the key value from the sorties table in the subquery, and then checks whether those key values exist in the sorties table in the outer statement. That check is superfluous; you can just compare the values to the ones in reports directly:

UPDATE sorties
SET state = '#'
WHERE key IN (SELECT sortieId
              FROM reports);

As for the second command, SQLite does not support aliasing a table used in INSERT/UPDATE/DELETE because those commands work only on a single table. You can just remove the AS s and replace s with sorties everywhere.

SQLite / MySQL compatibility issue

1 Answers