How delete table inner join with other table in Sqlite?

Question

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?

Answer 1

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
);

Answer 2

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
);