CODEWITHSUNDEEP
mysqlsqljoin
Onki
Onki

Reputation: 2095

why we provide alias names after 'DELETE' in multiple delete

This might be a very stupid questions, but I am keen to know if anyone has any suggestions:

Single Table Delete Correct Query : delete from vehicle_owner where id=3;

Single Table Delete InCorrect Query : delete from vehicle_owner v where v.id=3

Mulitple Table Delete : delete v,s from vehicle v , category s where v.id=3 and v.id=s.id;

Qs 1: I was wondering that why 1st is right and why 2nd is incorrect. Basically I am looking for logical answers which explains why providing an alias in delete query is incorrect.

Qs 2: why we keep two alias names after 'DELETE' keyword for multiple delete. Anyways we are providing complete details in join condition. So why it is designed in such a way.

Upvotes: 3

Views: 117

Answers (2)

siride
siride

Reputation: 210055

  1. The second is incorrect because if you use an alias, the SQL statement is considered a version of the multi-table DELETE that just happens to have only one table specified. As such, it must follow the other rules of multi-table DELETE. There's no room in the syntax for single-table DELETE to specify an alias.

  2. You don't need to have aliases, but you do need to specify which of the tables you are deleting from. So you list the tables, or their aliases, directly after the DELETE keyword. Otherwise, MySQL won't know whether to delete rows only from the first table (with the other tables being present to filter) or to delete from all tables, or some combination.

See: https://dev.mysql.com/doc/refman/5.5/en/delete.html

Upvotes: 2

Endareth
Endareth

Reputation: 512

The full correct syntax for your second example would be:

delete v from vehicle_owner as v where v.id = 3

Once you've specified the alias, you need to also specify it in the delete clause.

For your second question, I'm not quite sure what you're asking.

Upvotes: 1

Related Questions