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.)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
