SEO

#1 (**permalink**) 02-28-2008, 07:52 AM

I can successfully perform the query

SELECT * FROM `mos_availability` a WHERE NOT EXISTS (

SELECT *
FROM mos_users b
WHERE a.user = b.id)
But

DELETE FROM `mos_availability` a WHERE NOT EXISTS (

SELECT *
FROM mos_users b
WHERE a.user = b.id)

Says that I have an SQL syntax error.

Can anyone spot what it is for me?

TIA
Regards
Paul

#2 (**permalink**) 02-28-2008, 07:52 AM

Paul Lautman wrote:
> Says that I have an SQL syntax error.

My first guess is that you're using MySQL 4.0, which does not support
subqueries.

Regards,
Bill K.

#3 (**permalink**) 02-28-2008, 07:52 AM

Bill Karwin wrote:
> Paul Lautman wrote:
>> Says that I have an SQL syntax error.
>
> My first guess is that you're using MySQL 4.0, which does not support
> subqueries.
>
> Regards,
> Bill K.

Surely if it didn't support subqueries, the subquery

SELECT * FROM `mos_availability` a WHERE NOT EXISTS (

SELECT *
FROM mos_users b
WHERE a.user = b.id)

would not have worked???

#4 (**permalink**) 02-28-2008, 07:52 AM

Paul Lautman wrote:
> I can successfully perform the query

Ah, mea culpa, I posted my previous response hastily; obviously you are
using MySQL 4.1 if the subquery in the SELECT works.

I'd suggest this rewrite:

DELETE FROM `mos_availability` a WHERE a.user NOT IN (
SELECT b.id
FROM mos_users b

Or use MySQL's multi-table DELETE syntax:

DELETE a
FROM `mos_availability` AS a
LEFT JOIN `mos_users` AS b ON a.user = b.id
WHERE b.id IS NULL

Regards,
Bill K.

#5 (**permalink**) 02-28-2008, 07:52 AM

Bill Karwin wrote:
> Paul Lautman wrote:
>> I can successfully perform the query
>
> Ah, mea culpa, I posted my previous response hastily; obviously you
> are using MySQL 4.1 if the subquery in the SELECT works.
>
> I'd suggest this rewrite:
>
> DELETE FROM `mos_availability` a WHERE a.user NOT IN (
> SELECT b.id
> FROM mos_users b
>
> Or use MySQL's multi-table DELETE syntax:
>
> DELETE a
> FROM `mos_availability` AS a
> LEFT JOIN `mos_users` AS b ON a.user = b.id
> WHERE b.id IS NULL
>
> Regards,
> Bill K.

I realised that I could use the NOT IN syntax, but my experience (at least
with DB2) is that using NOT EXISTS is far more efficienct than the subselect
for NOT IN.

Hopefully the LEFT JOIN syntax is also efficient. I'd love to know why the
NOT EXISTS one works for a select, but not for a DELETE?

#6 (**permalink**) 02-28-2008, 07:52 AM

Paul Lautman wrote:
> I can successfully perform the query
>
> SELECT * FROM `mos_availability` a WHERE NOT EXISTS (
>
> SELECT *
> FROM mos_users b
> WHERE a.user = b.id)
> But
>
> DELETE FROM `mos_availability` a WHERE NOT EXISTS (
>
> SELECT *
> FROM mos_users b
> WHERE a.user = b.id)
>
> Says that I have an SQL syntax error.
>
> Can anyone spot what it is for me?
>
> TIA
> Regards
> Paul
>
>

You can't DELETE from a table you're referencing in the subquery.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#7 (**permalink**) 02-28-2008, 07:52 AM

On Wed, 28 Jun 2006 20:28:35 +0100, Paul Lautman wrote:
> Bill Karwin wrote:
>> Paul Lautman wrote:
>>> I can successfully perform the query
>>
>> Ah, mea culpa, I posted my previous response hastily; obviously you
>> are using MySQL 4.1 if the subquery in the SELECT works.
>>
>> I'd suggest this rewrite:
>>
>> DELETE FROM `mos_availability` a WHERE a.user NOT IN (
>> SELECT b.id
>> FROM mos_users b
>>
>> Or use MySQL's multi-table DELETE syntax:
>>
>> DELETE a
>> FROM `mos_availability` AS a
>> LEFT JOIN `mos_users` AS b ON a.user = b.id
>> WHERE b.id IS NULL
>>
>> Regards,
>> Bill K.
>
> I realised that I could use the NOT IN syntax, but my experience (at least
> with DB2) is that using NOT EXISTS is far more efficienct than the subselect
> for NOT IN.

The efficiency of a non-working query is not maximal.

> Hopefully the LEFT JOIN syntax is also efficient. I'd love to know why the
> NOT EXISTS one works for a select, but not for a DELETE?

It is, and I think rather more than NOT EXISTS.

--
91. I will not ignore the messenger that stumbles in exhausted and obviously
agitated until my personal grooming or current entertainment is finished.
It might actually be important.
--Peter Anspach's list of things to do as an Evil Overlord

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

DELETE where NOT EXISTS