CODEWITHSUNDEEP
sqlite
D T
D T

Reputation: 3746

How delete table inner join with other table in Sqlite?

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?

Upvotes: 58

Views: 35178

Answers (2)

Peter Trcka
Peter Trcka

Reputation: 1521

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

Upvotes: 27

Oleksandr Papchenko
Oleksandr Papchenko

Reputation: 2211

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

Upvotes: 78

Related Questions