No problem. I hope by now you figured out I made a typo... :) The
WHERE in the DELETE should be prod_price_chg_flag='O', not ="X" :)
-jay
Jerry Schwartz wrote:
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]
