List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 8 2003 4:42pm
Subject:Re: sql delete question
View as plain text  
At 7:27 -0500 1/8/03, Veysel Harun Sahin wrote:
>Hello,
>
>I have two tables whose structures are below.
>
>- Table1 -
>table1id int not null auto_increment
>data varchar(30)
>
>- Table2 -
>table2id int not null auto_increment
>table1id int not null
>data varchar(30)
>
>
>These two tables are connected to each other with the "table1id" 
>column. I need to delete rows in table1 which have no corresponding 
>"table1id" values in table2 and also i need to delete rows in table2 
>which have no corresponding "table1id" values in table1. Any 
>comments?
>
>Thanks.
>
>sql,query


This sounds like a "delete parentless children and childless parents"
question.  Turning to my handy copy of MySQL Cookbook which I just happen
to have nearby :-) I see the following under "Identifying and Removing
Unattached Records" (pp668-669), where the _head and _item are the parent
and child tables that correspond to your table1 and table2:

---------------
To  use  a  multiple-table  DELETE  statement  for  removing
unmatched records, just take the SELECT statement  that  you
use  to identify those records and replace the stuff leading
up to the FROM keyword with DELETE tbl_name.   For  example,
the  SELECT  that  identifies  childless  parents looks like
this:

      SELECT swdist_head.dist_id AS 'unmatched swdist_head IDs'
      FROM swdist_head LEFT JOIN swdist_item
          ON swdist_head.dist_id = swdist_item.dist_id
      WHERE swdist_item.dist_id IS NULL;

The corresponding DELETE looks like this:

      DELETE swdist_head
      FROM swdist_head LEFT JOIN swdist_item
          ON swdist_head.dist_id = swdist_item.dist_id
      WHERE swdist_item.dist_id IS NULL;

Conversely, the query to identify parentless children is  as
follows:

      SELECT swdist_item.dist_id AS 'unmatched swdist_item IDs'
      FROM swdist_item LEFT JOIN swdist_head
          ON swdist_item.dist_id = swdist_head.dist_id
      WHERE swdist_head.dist_id IS NULL;

And the corresponding DELETE statement removes them:

      DELETE swdist_item
      FROM swdist_item LEFT JOIN swdist_head
          ON swdist_item.dist_id = swdist_head.dist_id
      WHERE swdist_head.dist_id IS NULL;
---------------

This requires that you have MySQL 4.x, because 3.x doesn't support
multiple-table DELETE.  For 3.x, you must write a program that identifies
the unattached records and generates the appropriate statements to delete
them.  (The code for this is in the Cookbook, too; you can get it at
the book's web site.)
Thread
sql delete question(Veysel Harun Sahin)8 Jan
  • Re: sql delete questionPaul DuBois8 Jan
  • RE: sql delete questionAdolfo Bello8 Jan
RE: sql delete question(Veysel Harun Sahin)10 Jan