• andy chuks How to Delete Rows Across Multiple Mysql Tables?
    Share This, Tweet, +1 or Recommend   
    I have two tables containing similar data, I want to delete from one of the tables where the it contains same data as the other table. I used the script below an ran into bogus query that got my mysql server restarted. How to I do this better?

    DELETE table1.link FROM table1 INNER JOIN table2 WHERE table1.link=table2.link     01 November 2011Comment
    Share This, Tweet, +1 or Recommend   
    • ogugua belonwu >>>hold on, will be back with a reply<<<
      Like It0 Unlike It0 01 November 2011
    • andy chuks Ok, Thanks in Advance
      Like It0 Unlike It0 02 November 2011
    • shegun babs That's not the proper way to use DELETE in SQL queries. You can only use SELECT that way.
      You'll have to delete both records with separate SQL statements otherwise you should use a foreign key constraints to reference the foreign key and ON DELETE CASCADE.

      e.g
      CREATE TABLE parent (id INT NOT NULL,
      PRIMARY KEY (id)
      ) ENGINE=INNODB;

      CREATE TABLE child (id INT, parent_id INT,
      INDEX par_ind (parent_id),
      FOREIGN KEY (parent_id) REFERENCES parent(id)
      ON DELETE CASCADE
      ) ENGINE=INNODB;

      this way if you delete a record from "parent" table with id = 5,
      another record from "child" table with parent_id = 5 is also delete too.
      Like It0 Unlike It0 14 April 2012
    • ogugua belonwu @shegun babs, in this case a foreign key constraint will not work as there will be records that do not obey the foreign key on second table.
      Like It0 Unlike It0 14 April 2012