SQLite: Get error when try to delete with alias name

Question

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

If you remove the EXISTS condition, will you still get the error? — jarlh
– jarlh, Commented Dec 3, 2018 at 16:04
Now remove the complete WHERE clause. Keep DELETE FROM notification_invoice t1. Any error? Note, you will delete all data if it works, never try this on a production database! — jarlh
– jarlh, Commented Dec 3, 2018 at 16:06
DELETE FROM notification_invoice t1 ( SELECT 1 FROM notification_invoice t2 WHERE .... - get same error. — Alexei
– Alexei, Commented Dec 3, 2018 at 16:08

Shawn · Accepted Answer · 2018-12-03 20:33:52Z

2

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.

edited Dec 3, 2018 at 20:33

answered Dec 3, 2018 at 20:26

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alexei Over a year ago

Not help: DELETE FROM notification_invoice AS t1 WHERE notificationDate ... - Error: [SQLITE_ERROR] SQL error or missing database (near "DELETE": syntax error) SQLState: null ErrorCode: 1

Shawn Over a year ago

@Alexei what version of sqlite are you using?

Alexei Over a year ago

sqlite-jdbc-3.8.10.1.jar

Shawn Over a year ago

@Alexei that's pretty old.

Alexei Over a year ago

Is this a last version: 3.25.2 ?

|

MatBailie · Accepted Answer · 2018-12-03 16:20:33Z

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.

b2397 · Accepted Answer · 2018-12-03 16:16:27Z

0

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.

edited Dec 3, 2018 at 16:16

answered Dec 3, 2018 at 16:13

b2397

386 bronze badges

3 Comments

Alexei Over a year ago

If I'm not use alias "t1" - no error. But query return incorrect result.

MatBailie Over a year ago

@Alexei - If you SHOUT at us, do you really think we'll try to help you!?!?!??!?!

b2397 Over a year ago

Great, sounds like your question is answered. The incorrect result is very likely due to some logic error in your WHERE clause and EXISTS condition. You'll have to figure this out on your own.

Collectives™ on Stack Overflow

SQLite: Get error when try to delete with alias name

3 Answers 3

6 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related