"Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 01:07:00 PM:
> I apologize for not providing more information. I am currently > using 4.1.8 client and server on windows xp. > > ________________________________ > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, September 02, 2005 11:48 AM > To: Barbara Deaton > Cc: mysql@lists.mysql.com > Subject: Re: Where exists query syntax error? > > > > > "Barbara Deaton" <[EMAIL PROTECTED]> wrote on 09/02/2005 11:33:19 AM: > > > I need help re-writing a query. > > > > When accessing DB2 I can execute the below: > > delete from DM_KOHLS.clr_plan_price_change_metrics A > > where A.start_dt>= Date( '05/15/2005' ) and > > exists ( select * from SESSION.delete_table_tmp > B > > where A.clr_plan_sk = B.clr_plan_sk) > > > > > > I modified it to be what I thought was a valid MySQL statement but > > when I execute it against MySQL I get a syntax error: > > > > ERROR: Execute error: You have an error in your SQL syntax. Check > > the manual that corresponds > > to your MySQL server version for the right syntax to use near > > 'exists ( select * from > > delete_table_tmp where clr_plan_price_ch > > > > > > The statement I executed is: > > delete from clr_plan_price_change_metrics > > where start_dt>= '2005-05-15' and > > exists ( select * from delete_table_tmp > > where clr_plan_price_change_metrics. > > clr_plan_sk = delete_table_tmp.clr_plan_sk) > > > > > > I can successfully execute each piece of the query, but when I > > combine it with the 'exists' subquery it fails. > > > > The examples in the doc show just selects... Exists() so I tried > > changing it to a select from table and still fails. What is the > > correct syntax? > > -Barb. > > > > Your version, depending on how old it is, may not support the EXISTS > predicate. It may not support the subquery. It may not even support > deletes involving more than one table at a time. Please tell us > which version you are using so that we can give you a form of your > DELETE query compatible with your server. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine According to http://dev.mysql.com/doc/mysql/en/delete.html there are 3 valid forms of the DELETE statement. You seem to be trying to use the first one listed DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] delete from clr_plan_price_change_metrics where start_dt>= '2005-05-15' and exists ( select * from delete_table_tmp where clr_plan_price_change_metrics.clr_plan_sk = delete_table_tmp.clr_plan_sk ) This is documented to work for SELECT queries but I am not 100% certain it works in DELETE queries. I looked for a bug on this. I seem to remember seeing a change log entry saying something like this was fixed but my boolean query keeps timing out so I can not reference the change log entry or what version it was fixed in. Sorry. One way to actually do what you want to do is to NOT use a subquery and use either one of the "multi-table" forms. I prefer the first DELETE clr_plan_price_change_metrics FROM clr_plan_price_change_metrics INNER JOIN delete_table_tmp ON clr_plan_price_change_metrics.clr_plan_sk = delete_table_tmp.clr_plan_sk WHERE start_dt>= '2005-05-15' Shawn Green Database Administrator Unimin Corporation - Spruce Pine
