53

My query:

DELETE a FROM TR_ContactResultRecord  a
INNER JOIN TR_Case  b on (a.FireStationCode=b.FireStationCode and a.CaseNo=b.CaseCode )
WHERE b.Update_DateTime <=20140628134416

It show error: [Err] 1 - near "a": syntax error

How delete table inner join with other table in Sqlite?

D T
  • 3,522
  • 7
  • 45
  • 89
  • 1
    Sqlite does not accept this syntax unfortunately as being different from sql server. – oiyio Apr 25 '18 at 11:22
  • PostgreSQL docs suggest `DELETE JOIN` is not part of the SQL standard BTW: https://www.postgresql.org/docs/14/sql-delete.html "This syntax is not standard. A more standard way to do it is" with `IN`. However it also states "In some cases the join style is easier to write or faster to execute than the sub-select style." so it is a shame that SQLite doesn't support it, even if just for greater portability. Related for UPDATE: https://stackoverflow.com/questions/19270259/update-with-join-in-sqlite – Ciro Santilli OurBigBook.com Nov 03 '21 at 20:19

2 Answers2

73

Try to rewrite you query using subquery: In case your PK for TR_ContactResultRecord is CaseNo

DELETE FROM TR_ContactResultRecord
WHERE CaseNo IN (
  SELECT CaseNo FROM TR_ContactResultRecord a
  INNER JOIN TR_Case b
    ON (a.FireStationCode=b.FireStationCode and a.CaseNo=b.CaseCode )
  WHERE b.Update_DateTime <=20140628134416
);
ManuelSchneid3r
  • 15,850
  • 12
  • 65
  • 103
Oleksandr Papchenko
  • 2,071
  • 21
  • 30
  • 28
    Oh, so SQLite doesn't like `DELETE FROM table JOIN ...` ? – Michael Feb 27 '16 at 22:48
  • 3
    I get this error too, it seems sqlite doesn't support delete inner join – adrian4aes Apr 26 '16 at 14:03
  • 2
    The select statement should probably be **a.CaseNo** instead of **CaseNo**. Otherwise great answer! – Optavius Dec 04 '18 at 07:31
  • 1
    @Optavius: a.CaseNo or CaseNo is ok. because in b table has not column CaseNo. – D T Jun 04 '19 at 02:31
  • Using this answer in conjunction with the ROWID automatically generated column mentioned in another answer was exactly what I needed to quickly check overlap between two tables using multiple criteria in the join, but a single column for the IN portion of the delete. – Shaun-Adam Apr 27 '22 at 02:17
24

SQLite has special ROWID column by default in each table. From official documentation:

You can access the ROWID of an SQLite table using one of the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.

The code is following:

DELETE FROM TR_ContactResultRecord
WHERE ROWID IN (
  SELECT a.ROWID FROM TR_ContactResultRecord a
  INNER JOIN TR_Case b
    ON (a.FireStationCode=b.FireStationCode and a.CaseNo=b.CaseCode )
  WHERE b.Update_DateTime <=20140628134416
);
Jaroslav Bezděk
  • 6,967
  • 6
  • 29
  • 46
Peter Trcka
  • 1,279
  • 1
  • 16
  • 21