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.
Best How To :
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.