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

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
>
>
>
>
>

Hy , i´ve done some search regarding your issue and i think you may
find this interesting :


http://forums.devshed.com/mysql-...0t-446448.html
http://dev.mysql.com/doc/refman/5.0/...am-tables.html

2007/12/19, Jerry Schwartz <jschwartz@the-infoshop.com>:
>
> 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/mysql?unsub=m...oria@gmail.com
>
>


--
Rodrigo Marins

Gerência de Controle e Desempenho de Sistemas
GRUPO TELEFÔNICA NO BRASIL
Rua Xavier Curado,473 9° andar
CEP 04210-100 São Paulo - SP
Tel: 55 11-6166-3163 / 8152-1112
marins.consultoria@gmail.com
http://www.telefonica.com.br
-
PgConBrasil: dias 7-8 dezembro 2007
http://pgcon.postgresql.org.br

> > 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.
>
> Doesn't really make any sense. Isn't it saying to delete records
> where:
>
> WHERE prod_price_chg_flag = "0"
> AND prod_price_chg_flag = "X"
>
[JS] No. prod_id is not a unique key. If this were legal, it would select
all products that have an "X" price and then delete all "O" prices from
those products.

Two or more price records having the same prod_id, that's what I meant by
"accompanying".


> What do you mean by "accompanying" record? Accompanying in a
> _different_
> table? Or maybe you have some other criteria for determining
> "accompanying"?

I did a CHECK TABLE and it reported no errors.



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

From: Rodrigo Marins [mailto:marins.consultoria@gmail.com]
Sent: Wednesday, December 19, 2007 7:07 PM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Forbidden subquery



Hy , i´ve done some search regarding your issue and i think you may
find this interesting :


<http://forums.devshed.com/mysql-...rage-engine-er
ror-number-1030t-446448.html>
http://forums.devshed.com/mysql-...age-engine-err
or-number-1030t-446448.html
http://dev.mysql.com/doc/refman/5.0/...am-tables.html

2007/12/19, Jerry Schwartz < jschwartz@the-infoshop.com>:

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/mysql?unsub=m...oria@gmail.com




--
Rodrigo Marins

Gerência de Controle e Desempenho de Sistemas
GRUPO TELEFÔNICA NO BRASIL
Rua Xavier Curado,473 9° andar
CEP 04210-100 São Paulo - SP
Tel: 55 11-6166-3163 / 8152-1112
marins.consultoria@gmail.com
http://www.telefonica.com.br
-
PgConBrasil: dias 7-8 dezembro 2007
http://pgcon.postgresql.org.br

>
> 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/mysql?unsub=jschwartz@the-
> infoshop.com

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/mysql?unsub=jschwartz@the-
>> infoshop.com
>
>
>
>
>

> -----Original Message-----
> From: Jay Pipes [mailto:jay@mysql.com]
> Sent: Thursday, December 20, 2007 11:25 AM
> To: Jerry Schwartz
> Cc: mysql@lists.mysql.com
> Subject: Re: Forbidden subquery
>
> 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"
>
[JS] Actually, I didn't look at your solution that closely because in the
meantime my problem got a lot more complicated. I'm going to have to match
on more than one field, so I won't be able to delete all of the "O" records.
I'm going to have to match on a varchar field as well, and do manual cleanup
of any typos.

Your temp-table solution is still going to be at the heart of my work.