Reputation: 2095
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
Reputation: 210055
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.
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
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