DELETE sql with JOIN

Databases
1

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';
            $this->db->query("
            DELETE table_1, table_2, table_3, table_4, table_5
            FROM tour_foreign
                JOIN  table_2
                ON table_1.id = table_2.rela
                JOIN  table_3
                ON table_2.h_id = table_3.h_id          
                JOIN  table_4
                ON table_1.id = table_4.rela
                JOIN  table_5               
                ON table_2.h_id = table_5.h_id
            WHERE table_1.id =".floor($id));
2

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

3

because you’re referencing a table (table_1) that doesn’t exist in the FROM clause

4

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?

5

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

6

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.

7

Ok, Make a mistake, i want delete rows in tables. how is it?
This is my real table and column names:


$this->db->query("
		    DELETE tour_foreign
			FROM tour_foreign
			    JOIN  tour_foreign_residence
				ON tour_foreign.id = tour_foreign_residence.relation
			    JOIN  tour_foreign_units
				ON tour_foreign_units.hotel_id = tour_foreign_residence.hotel_id			
			    JOIN  tour_foreign_image
				ON tour_foreign.id = tour_foreign_image.relation
			    JOIN  tour_foreign_prices_changing			    
				ON tour_foreign_prices_changing.hotel_id = tour_foreign_residence.hotel_id
			WHERE tour_foreign.id = $id));
8

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?

9

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.

10

Who can help me for my problem, there is a guy? I don’t get still answers for Question’s.

11

of course you do

you have been getting answers all day

:smiley: