MySQL Error 1064 when DELETEing
 

I'm trying to carry out the following delete statements

DELETE FROM images AS i WHERE i.image_id = 803 AND i.plant_num = 2277 LIMIT 1

DELETE FROM images AS i WHERE i.image_id = 804 AND i.plant_num = 2277 LIMIT 1

DELETE FROM images AS i WHERE i.image_id = 805 AND i.plant_num = 2277 LIMIT 1

but i am getting the 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 'AS i WHERE i.image_id = 803 AND i.plant_num = 2277 LIMIT 1; DELETE FROM images A' at line 1

any idea what is causing this because everything looks okay to me.

Thanks in advance

Re: MySQL Error 1064 when DELETEing
 

Write statements without aliase e.g.

DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;

Re: MySQL Error 1064 when DELETEing
 

thanks, i tried doin that but still get the same basic error.

Query:

DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;

DELETE FROM images WHERE image_id = 804 AND plant_num = 2277 LIMIT 1;

DELETE FROM images WHERE image_id = 805 AND plant_num = 2277 LIMIT 1;

Error:

Could not update the database:

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 '; DELETE FROM images WHERE image_id = 804 AND plant_num = 2277 LIMIT 1; DELETE F' at line 1

and here's a PHP snippet:

foreach($removals as $id)

        $sql .= "\nDELETE FROM images WHERE image_id = ".$id." AND plant_num = ".$_REQUEST['pid']." LIMIT 1;";

Re: MySQL Error 1064 when DELETEing
 

What does your PHP query statement print out as? I'm betting it's not what you think it is.

Also, a

describe images

would help us help you.

Re: MySQL Error 1064 when DELETEing
 

well the query i showed you is what prints out.

DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;

DELETE FROM images WHERE image_id = 804 AND plant_num = 2277 LIMIT 1;

DELETE FROM images WHERE image_id = 805 AND plant_num = 2277 LIMIT 1;

and describe images is:

Field                Type                            Null         Key         Default         Extra

image_id          int(10) unsigned            NO         PRI         NULL         auto_increment

plant_num        int(10) unsigned            NO         MUL         NULL          

image_src                varchar(255)            NO                   NULL          

image_alt                varchar(64)            NO                   NULL          

location_taken  varchar(64)            YES                   Barbados          

is_vouchered    tinyint(1)            NO                   0          

is_best_fit                tinyint(1)            NO                   0          

Re: MySQL Error 1064 when DELETEing
 

What does

SELECT * FROM images WHERE image_id = 803 AND plant_num = 2277\G

show you? If you can't find that image, then obviously you can't delete it.

Re: MySQL Error 1064 when DELETEing
 

nope i get back a result

btw i'm connecting as a user with delete priviliges so it isnt that either

i thought it might be the "\n" in the sql string perhaps but i took it out and still got the error

thanks for the assistance so far, i hope someone can help cause i reallly need to try and get this finished up soon

Re: MySQL Error 1064 when DELETEing
 

Can you execute

DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;

from the command line?

Can you execute that literal string from your script?

Your script might not have the same permissions you do from the command line. Do other of your scripts execute OK when accessing the database?

1064 seems to refer to using a reserved word, but I see none in your query.

Re: MySQL Error 1064 when DELETEing
 

Hi thanks for all the previous help but i've figured it out.

This is the php/sql query i use now:

php:

$sql .= "\nDELETE FROM images WHERE plant_num = ".$_REQUEST['pid']." AND image_id IN (".implode(",",$removals).");";

query

DELETE FROM images WHERE plant_num = 2277 AND image_id IN (803,804,805);

I'm not sure why, but MySQL didnt seem to like when i put DELETE statements one after the other like that

Re: MySQL Error 1064 when DELETEing
 

Huh. Yes that is very strange. Glad you got it figured out though. And thanks for posting the solution in case someone else runs into this.