469,621 Members | 1,685 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,621 developers. It's quick & easy.

trying a delete with subquery

What is wrong with this? IT gives this error:
#1064 - 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
'SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN ren

The select portion does what I want. test is a linking table between a
rentalproperties and a photos. I want to delete all the photos associated
with a certain property

New to MySQL, reading the manual and the o'reilly sql in a nutshell. Going
MAD!

DELETE FROM pp WHERE (
SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN rentalproperties AS r ON r.ndx = pp.propertyindex
JOIN photos AS p ON pp.photoindex = p.photoindex
WHERE pp.propertyindex = 4
)

--
Joel Goldstick
columbuswebmakers.com
Jul 19 '05 #1
6 2271
Hiya Joel -
I had thought nested selects were not allowed in mySql ?
I could be wrong, of course . I DO SEE that you are using alias 'pp' there
a lot .
Did you mean to ?

I do a lot of client server work with VFP and mySql - so what I normally do
is make a vfp
table of just the pk's that I want to delete, the
do a scan..endscan operation in vfp , automatically generating the sql code
I want to send over,
and then send it over the wire in an odbc hook / technique called sql pass
through .

I noticed about 18 months ago [when I tried] that it wasn't possible to do a
nested select - so I gave up on trying anymore and moved on to data munging
in VFP.

I do know if you were using Oracle or MS-SQL - a nested select for delete
would work.
Anyway - check your usage on alias name of 'pp' - see where you have it
overused, change the name a few times, and see if that is meaningful.

IF IT'S NOT - then perhaps nested selects are still not allowed.
Which version of mySql are you using ?
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"Joel Goldstick" <jo************@columbuswebmakers.com> wrote in message
news:2m******************@fe1.columbus.rr.com...
What is wrong with this? IT gives this error:
#1064 - 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
'SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN ren

The select portion does what I want. test is a linking table between a
rentalproperties and a photos. I want to delete all the photos associated
with a certain property

New to MySQL, reading the manual and the o'reilly sql in a nutshell. Going MAD!

DELETE FROM pp WHERE (
SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN rentalproperties AS r ON r.ndx = pp.propertyindex
JOIN photos AS p ON pp.photoindex = p.photoindex
WHERE pp.propertyindex = 4
)

--
Joel Goldstick
columbuswebmakers.com

Jul 19 '05 #2
Hiya Joel -
I had thought nested selects were not allowed in mySql ?
I could be wrong, of course . I DO SEE that you are using alias 'pp' there
a lot .
Did you mean to ?

I do a lot of client server work with VFP and mySql - so what I normally do
is make a vfp
table of just the pk's that I want to delete, the
do a scan..endscan operation in vfp , automatically generating the sql code
I want to send over,
and then send it over the wire in an odbc hook / technique called sql pass
through .

I noticed about 18 months ago [when I tried] that it wasn't possible to do a
nested select - so I gave up on trying anymore and moved on to data munging
in VFP.

I do know if you were using Oracle or MS-SQL - a nested select for delete
would work.
Anyway - check your usage on alias name of 'pp' - see where you have it
overused, change the name a few times, and see if that is meaningful.

IF IT'S NOT - then perhaps nested selects are still not allowed.
Which version of mySql are you using ?
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"Joel Goldstick" <jo************@columbuswebmakers.com> wrote in message
news:2m******************@fe1.columbus.rr.com...
What is wrong with this? IT gives this error:
#1064 - 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
'SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN ren

The select portion does what I want. test is a linking table between a
rentalproperties and a photos. I want to delete all the photos associated
with a certain property

New to MySQL, reading the manual and the o'reilly sql in a nutshell. Going MAD!

DELETE FROM pp WHERE (
SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN rentalproperties AS r ON r.ndx = pp.propertyindex
JOIN photos AS p ON pp.photoindex = p.photoindex
WHERE pp.propertyindex = 4
)

--
Joel Goldstick
columbuswebmakers.com

Jul 19 '05 #3
Hiya Joel -
I had thought nested selects were not allowed in mySql ?
I could be wrong, of course . I DO SEE that you are using alias 'pp' there
a lot .
Did you mean to ?

I do a lot of client server work with VFP and mySql - so what I normally do
is make a vfp
table of just the pk's that I want to delete, the
do a scan..endscan operation in vfp , automatically generating the sql code
I want to send over,
and then send it over the wire in an odbc hook / technique called sql pass
through .

I noticed about 18 months ago [when I tried] that it wasn't possible to do a
nested select - so I gave up on trying anymore and moved on to data munging
in VFP.

I do know if you were using Oracle or MS-SQL - a nested select for delete
would work.
Anyway - check your usage on alias name of 'pp' - see where you have it
overused, change the name a few times, and see if that is meaningful.

IF IT'S NOT - then perhaps nested selects are still not allowed.
Which version of mySql are you using ?
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"Joel Goldstick" <jo************@columbuswebmakers.com> wrote in message
news:2m******************@fe1.columbus.rr.com...
What is wrong with this? IT gives this error:
#1064 - 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
'SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN ren

The select portion does what I want. test is a linking table between a
rentalproperties and a photos. I want to delete all the photos associated
with a certain property

New to MySQL, reading the manual and the o'reilly sql in a nutshell. Going MAD!

DELETE FROM pp WHERE (
SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN rentalproperties AS r ON r.ndx = pp.propertyindex
JOIN photos AS p ON pp.photoindex = p.photoindex
WHERE pp.propertyindex = 4
)

--
Joel Goldstick
columbuswebmakers.com

Jul 19 '05 #4
I have had similar problems in the past.

The easiest way I have found is to do the initial select, pass this to
an array and then manipulate the array with a second SQL statement.

Seems to work for me, just fine.

"Joel Goldstick" <jo************@columbuswebmakers.com> wrote in message news:<2m******************@fe1.columbus.rr.com>...
What is wrong with this? IT gives this error:
#1064 - 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
'SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN ren

The select portion does what I want. test is a linking table between a
rentalproperties and a photos. I want to delete all the photos associated
with a certain property

New to MySQL, reading the manual and the o'reilly sql in a nutshell. Going
MAD!

DELETE FROM pp WHERE (
SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN rentalproperties AS r ON r.ndx = pp.propertyindex
JOIN photos AS p ON pp.photoindex = p.photoindex
WHERE pp.propertyindex = 4
)

Jul 19 '05 #5
I have had similar problems in the past.

The easiest way I have found is to do the initial select, pass this to
an array and then manipulate the array with a second SQL statement.

Seems to work for me, just fine.

"Joel Goldstick" <jo************@columbuswebmakers.com> wrote in message news:<2m******************@fe1.columbus.rr.com>...
What is wrong with this? IT gives this error:
#1064 - 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
'SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN ren

The select portion does what I want. test is a linking table between a
rentalproperties and a photos. I want to delete all the photos associated
with a certain property

New to MySQL, reading the manual and the o'reilly sql in a nutshell. Going
MAD!

DELETE FROM pp WHERE (
SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN rentalproperties AS r ON r.ndx = pp.propertyindex
JOIN photos AS p ON pp.photoindex = p.photoindex
WHERE pp.propertyindex = 4
)

Jul 19 '05 #6
I have had similar problems in the past.

The easiest way I have found is to do the initial select, pass this to
an array and then manipulate the array with a second SQL statement.

Seems to work for me, just fine.

"Joel Goldstick" <jo************@columbuswebmakers.com> wrote in message news:<2m******************@fe1.columbus.rr.com>...
What is wrong with this? IT gives this error:
#1064 - 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
'SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN ren

The select portion does what I want. test is a linking table between a
rentalproperties and a photos. I want to delete all the photos associated
with a certain property

New to MySQL, reading the manual and the o'reilly sql in a nutshell. Going
MAD!

DELETE FROM pp WHERE (
SELECT pp.propertyindex, pp.photoindex
FROM test AS pp
JOIN rentalproperties AS r ON r.ndx = pp.propertyindex
JOIN photos AS p ON pp.photoindex = p.photoindex
WHERE pp.propertyindex = 4
)

Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Vikram Vaswani | last post: by
2 posts views Thread by Rotsj | last post: by
5 posts views Thread by Mike Wiseley | last post: by
8 posts views Thread by Michel Esber | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.