CODEWITHSUNDEEP
sqlsqlite
Alexei
Alexei

Reputation: 15674

SQLite: Get error when try to delete with alias name

In SQLite my query:

DELETE FROM notification_invoice t1 WHERE notificationDate >= 1536883200000  and providerId in ("1234","5678") 
    AND EXISTS (
         SELECT 1 FROM notification_invoice t2  WHERE 
         providerId in ("1234","5678") 
         and t2.notificationDate = t1.notificationDate          
         and t1.ownerKey = t2.ownerKey 
         AND t1._id < t2._id
    )

But I get error:

Error: [SQLITE_ERROR] SQL error or missing database (near "t1": syntax error)
SQLState:  null
ErrorCode: 1

Upvotes: 0

Views: 544

Answers (3)

Shawn
Shawn

Reputation: 52449

Sqlite does support table aliases with DELETE, you're just using the wrong syntax. You need to use AS between the table name and alias:

sqlite> CREATE TABLE foo(bar);
sqlite> INSERT INTO foo VALUES ('dog');
sqlite> SELECT * FROM foo;
bar       
----------
dog       
sqlite> DELETE FROM foo AS f WHERE f.bar = 'dog';
sqlite> SELECT * FROM foo;
sqlite>

If you look at the syntax diagrams in the documentation for DELETE, in particular the qualified-table-name one, you'll see that the AS isn't optional like it is in a SELECT table name.

Upvotes: 2

MatBailie
MatBailie

Reputation: 86745

Removing the use of the alias, on the table being deleted from, will fix the syntax error.

DELETE FROM notification_invoice
WHERE notificationDate >= 1536883200000
  AND providerId in ("1234","5678") 
  AND EXISTS (
         SELECT 1
           FROM notification_invoice t2
          WHERE t2.providerId in ("1234","5678") 
            AND t2.notificationDate = notification_invoice.notificationDate          
            AND t2.ownerKey         = notification_invoice.ownerKey 
            AND t2._id              > notification_invoice._id
    )

Whether the logic is correct is impossible to say as you haven't described the data or the logic you index to implement.

Maybe the sub-query should have t2.providerId = notification_invoice.provider_id. We can't tell, without knowing the data, the constraints, the intended logic, etc, etc.

Upvotes: 1

b2397
b2397

Reputation: 38

The DELETE statement operates on a single table and can not use a table alias. The alias is causing your error.

See stackoverflow.com/a/15832338/2577062 for a similar situation.

Upvotes: 0

Related Questions