> > 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." > [JS] Yes, I knew that. I just thought that illegal query was the best way of expressing what I wanted to do.
> But, to bypass that, you can create a temp table and join to that: > [JS] Bingo! It didn't occur to me to make a temporary table. That should do exactly what I want! Thanks. > 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] > infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
