RE: sql delete question
Hello Adolfo, Yes I have tried it too but it did not work. Anyway i have done it programatically in my app. Thanks. Adolfo Bello [EMAIL PROTECTED] wrote: 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? Can you try a multi table DELETE? I haven't done it but I would be a shot at something like (adapted from MySQL Manual) DELETE t1 FROM t1,t2 WHERE t1.id=t2.id AND t2.id IS NULL Adolfo - 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 __ 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
Re: sql delete question
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
Re: sql delete question - solved
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
RE: sql delete question
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? Can you try a multi table DELETE? I haven't done it but I would be a shot at something like (adapted from MySQL Manual) DELETE t1 FROM t1,t2 WHERE t1.id=t2.id AND t2.id IS NULL Adolfo - 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
