SQLite delete with table alias [duplicate]

Question

I am trying to alias a table in SQLite, for example by the following command: (it is from the book i am reading"Database Management Systems by Ramakrishnan")

DELETE FROM Students S WHERE S.sid=12546

This code gives a syntax error. Without aliasing, the following code works:

DELETE FROM Students WHERE sid=12546

But, if i want to alias the table, what should i do? Can anyone help?

Thanks

@bluefeet i solved the delete error there but did not get the answer about aliasing — yrazlik
– yrazlik, Commented Apr 5, 2013 at 11:13

Ajo Koshy · Accepted Answer · 2013-04-05 11:14:38Z

4

The DELETE statement operates on a single table and does not use a table alias. so you will have to use your query as :

DELETE FROM Students WHERE sid=12546

Update: SQLite apparently doesn't support joins with the delete statement, as you can see on the Syntax diagrams. In short in SQLite, one DELETE command deletes from only one table. So aliasing is of no use

edited Apr 5, 2013 at 11:14

answered Apr 5, 2013 at 11:07

Ajo Koshy

1,2252 gold badges21 silver badges33 bronze badges

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

6 Comments

yrazlik Over a year ago

Thanks, but in the book i am reading "Database Management Systems by Ramakrishnan" it is written like this. Do you think it is wrong?

Ajo Koshy Over a year ago

SQLite apparently doesn't support joins with the delete statement, as you can see on the Syntax diagrams. So aliasing seems disabled in delete statement

CL. Over a year ago

@bigO Yes, that book is wrong for standard SQL or SQLite. Is it written for another DB like MySQL?

Jake Cobb Over a year ago

Actually the syntax diagram indicates that it should accept an alias, because it uses qualified-table-name and an alias would be useful because expr can include sub-queries, e.g: DELETE FROM foo AS f WHERE EXISTS (SELECT b.id FROM bar AS b WHERE b.key=f.id). This seems like an error in either implementation or syntax documentation.

Alexei Over a year ago

Here correct query (example) without alias : 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 )

|

Collectives™ on Stack Overflow

SQLite delete with table alias [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related