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
