Hi Jerry! The very last sentence on: http://dev.mysql.com/doc/refman/5.0/en/delete.html
is "Currently, you cannot delete from a table and select from the same table in a subquery."
But, to bypass that, you can create a temp table and join to that: CREATE TEMPORARY TABLE to_delete SELECT prod_price.prod_id FROM prod_price WHERE prod_price.prod_price_chg_flag = "X"; DELETE prod_price FROM prod_price JOIN to_delete ON prod_price.prod_id=to_delete.prod_id WHERE prod_price.prod_price_chg_flag = 'X'; DROP TABLE to_delete; Cheers, Jay Jerry Schwartz wrote:
What I want to accomplish is expressed best as DELETE FROM prod_price WHERE prod_price.prod_price_chg_flag = "O" AND prod_price.prod_id IN (SELECT prod_price.prod_id FROM prod_price WHERE prod_price.prod_price_chg_flag = "X") ; This is clear, concise, and completely illegal. I want to delete every "O" record which has an accompanying "X" record. I tried using a self-join like this DELETE FROM p1 USING prod_price AS p1 JOIN prod_price AS p2 ON p1.prod_id = p2.prod_id WHERE p1.prod_price_chg_flag = "O" AND p2.prod_price_chg_flag = "X" ; And got a storage engine error 134 (MyISAM table). I'm not even certain that this would have done what I wanted, but I guess I won't find out. Here's what the table prod_price looks like: Table: prod_price Create Table: CREATE TABLE `prod_price` ( `prod_price_id` varchar(15) NOT NULL default '', `prod_id` varchar(15) default NULL, `prod_price_del_format` varchar(255) default NULL, `prod_price_val_date` date default NULL, `prod_price_chg_flag` char(1) default NULL, `prod_price_disp_curr` varchar(10) default NULL, `prod_price_disp_price` decimal(10,2) default NULL, `prod_price_end_curr` varchar(10) default NULL, `prod_price_end_price` decimal(10,2) default NULL, `prod_price_reg_price` varchar(5) default NULL, `prod_price_changed` tinyint(1) default NULL, `prod_price_added` datetime default NULL, `prod_price_updated` datetime default NULL, PRIMARY KEY (`prod_price_id`), KEY `prod_id` (`prod_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I'm running 5.0.45-community-nt. Suggestions? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]