Hello Paul, I have tried the code you have written, before posting to list and it did not work. When I have seen the sentence "This requires that you have MySQL 4.x" at your mail I realized the problem. I am using mySQL 3.23.49. :(
Thanks for your help. Paul DuBois <[EMAIL PROTECTED]> wrote: >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.) > __________________________________________________________________ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ --------------------------------------------------------------------- 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
