Why don’t work this query for delete?(How can done best SQL for delete following five table)?
//$id = $this->input->post('id');
$id = '65';
DELETE table_1, table_2, table_3, table_4, table_5
ON table_1.id = table_2.rela
ON table_2.h_id = table_3.h_id
ON table_1.id = table_4.rela
ON table_2.h_id = table_5.h_id
WHERE table_1.id =".floor($id));
because when you join things you make a temporary table that cannot be edited.
What you’re looking for is a FOREIGN KEY with ON DELETE CASCADE
because you’re referencing a table (table_1) that doesn’t exist in the FROM clause
i’m not sure you actually meant to say this – maybe you meant to say something else, but what you said doesn’t make sense
first of all, there is no editing involved, just deleting joined rows
secondly, if you believe that the join produces a temporary table from which no rows can be deleted, can you explain why the mysql engineers implemented the following syntax?
Do you can give me example from my query in first post’s?
table_1.id => this have id 65
table_2.rela => this have rela 65
table_2.h_id => this have h_id 11
table_3.h_id => this have h_id 11
table_4.rela => this have rela 65
table_5.h_id => this have h_id 11
it would be a ~lot~ better for everyone if you stopped using table_1, table_2, etc.
please use the real table and column names, like tour_foreign, tour_foreign_residence, etc.
Ok, Make a mistake, i want delete rows in tables. how is it?
This is my real table and column names:
ON tour_foreign.id = tour_foreign_residence.relation
ON tour_foreign_units.hotel_id = tour_foreign_residence.hotel_id
ON tour_foreign.id = tour_foreign_image.relation
ON tour_foreign_prices_changing.hotel_id = tour_foreign_residence.hotel_id
WHERE tour_foreign.id = $id));
if you are going to delete only one particular row in the tour_foreign table, you probably don’t need the joins at all!!
however, i’ll bet you want to delete all related rows in the other tables as well, yes?
No all related rows, in tour_foreign.id one row have 65 and also is for tour_foreign_residence and tour_foreign_image that have relation 65 (tour_foreign.id = tour_foreign_residence.relation => 65 = 65, tour_foreign.id = tour_foreign_image.relation => 65 = 65,) now in tour_foreign_residence we have a column name hotel_id that have number 11 and it is set with hotel_id in tables[tour_foreign_units, tour_foreign_prices_changing].
How with there this relationship can get a id from tour_foreign and delete other rows in other tables.
i mane is this: (with have one id from tour_foreign we can delete other rows relationship with it in tables their, in following is a example from this relationship)
tour_foreign.id = 65 => tour_foreign_image.relation = 65 => tour_foreign_residence.relation = 65 -> tour_foreign_residence.hotel_id = 11 => tour_foreign_units.hotel_id = 11 => tour_foreign_prices_changing.hotel_id = 11.
Who can help me for my problem, there is a guy? I don’t get still answers for Question’s.
of course you do
you have been getting answers all day