CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
JimBo
JimBo

Reputation: 137

SQL DELETE with JOIN

Why the following query doesn't work? What is the best way to delete the following five tables row in SQL? How can I fix it?

//$id = $this->input->post('id');
$id = '65';
    $this->db->query("
        DELETE table_1, table_2, table_3, table_4, table_5
        FROM table_1
            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));

Update:

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

Update 2:

in table_1.id one row have 65 and also is for table_2 and table_4 that have rela 65 (table_1.id = table_2.rela => 65 = 65, table_1.id = table_4.rela => 65 = 65) now in table_2 we have a column name h_id that have number 11 and it is set with h_id in other tables[table_3, table_5].

How with there this relationship can get a id from table_1 and delete other rows in their tables.

i mane is this: (with have one id from table_1 we can delete other rows relationship with it in their tables, in following is a example from this relationship)

table_1.id = 65 => table_4.rela = 65 => table_2.rela = 65 -> table_2.h_id = 11 => table_3.h_id = 11 => table_5.h_id = 11.

UPDATE 3:

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

Upvotes: 0

Views: 472

Answers (3)

Mike Purcell
Mike Purcell

Reputation: 19989

Not sure why you are trying to do this at the app layer. MySQL provides the ability to delete child rows using foreign key constraints (ON DELETE CASCADE). Check your current schema and see if they are already available: 

mysql> show create table child_table;

If there is a foreign key constraint available you should see something like:

CONSTRAINT `constraint_id` FOREIGN KEY (`some_id`) REFERENCES `some_table` (`id`) ON DELETE CASCADE

What this means is if you delete a row from 'some_table', any rows in 'child_table' which reference the same id as 'some_table' will automatically be deleted.

http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for more info.

If you need to do this at the app layer then you should really consider a transaction, I am assuming you are using PDO, although it may work with mysqli. Below is a potential solution, as such I assume no responsibility for any data loss you may incur. 

$this->db->beginTransaction();

try {

   $this->db->execute('DELETE FROM table_1 WHERE id = 65');
   $this->db->execute('DELETE FROM table_2 WHERE rela = 65');
   $this->db->execute('DELETE FROM table_3 WHERE h_id = 11');
   $this->db->execute('DELETE FROM table_4 WHERE rela = 65');
   $this->db->execute('DELETE FROM table_5 WHERE h_id = 11');

   $this->db->commit();

} catch (Exception $e) {

    error_log($e->getMessage());

    $this->db->rollBack();

    $e = null; // PHP is notorious for not cleaning exceptions very well so force it
}

Upvotes: 0

Sarel Botha
Sarel Botha

Reputation: 12700

You say you want to delete the tables, but you probably mean you want to delete the row from the tables. Deleting or dropping a table means removing the table and all of its contents.

I see one problem. You mention table_1 in the first part, but not again. I'll assume the first table is table_1 and not tour_foreign.

    DELETE table_1, table_2, table_3, table_4, table_5
    FROM table_1
        INNER JOIN  table_2   ON table_1.id = table_2.rela
        INNER JOIN  table_3   ON table_2.h_id = table_3.h_id          
        INNER JOIN  table_4   ON table_1.id = table_4.rela
        INNER JOIN  table_5   ON table_2.h_id = table_5.h_id
    WHERE table_1.id = 5

From the mysql docs it looks like you can also leave out the list of tables if you want to delete the rows from all of those tables:

    DELETE FROM table_1
        INNER JOIN  table_2   ON table_1.id = table_2.rela
        INNER JOIN  table_3   ON table_2.h_id = table_3.h_id          
        INNER JOIN  table_4   ON table_1.id = table_4.rela
        INNER JOIN  table_5   ON table_2.h_id = table_5.h_id
    WHERE table_1.id = 5

Upvotes: 0

dknaack
dknaack

Reputation: 60486

You have to delete the rows of your tables one by one.

Its also possible to define your table for cascade delete... If you do this you only need delete only one row in one table. The other related rows in other table will deleted automaticly.

        CREATE TABLE `tableName` (
        `id` int(10) unsigned NOT NULL auto_increment,
        `typeId` int(10) unsigned NOT NULL,
        PRIMARY KEY (`id`),
        KEY `type` (`typeId`)
        CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
          REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

hope this helps

Upvotes: 1

Related Questions