943,915 Members | Top Members by Rank

Web Development > Databases > MySQL > MySQL Error 1064 when DELETEing
Ad:
  • MySQL Discussion Thread
  • Marked Solved
  • Views: 4452
  • MySQL RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
May 7th, 2008
0

MySQL Error 1064 when DELETEing

Expand Post »
I'm trying to carry out the following delete statements
MySQL Syntax (Toggle Plain Text)
  1. DELETE FROM images AS i WHERE i.image_id = 803 AND i.plant_num = 2277 LIMIT 1
  2. DELETE FROM images AS i WHERE i.image_id = 804 AND i.plant_num = 2277 LIMIT 1
  3. DELETE FROM images AS i WHERE i.image_id = 805 AND i.plant_num = 2277 LIMIT 1

but i am getting the error:
MySQL Syntax (Toggle Plain Text)
  1. 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
Last edited by ray_broome; May 7th, 2008 at 3:21 pm.
Reputation Points: 10
Solved Threads: 0
Light Poster
ray_broome is offline Offline
25 posts
since Aug 2004
Permalink
May 7th, 2008
0

Re: MySQL Error 1064 when DELETEing

Write statements without aliase e.g.
MySQL Syntax (Toggle Plain Text)
  1. DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;
Reputation Points: 29
Solved Threads: 47
Posting Whiz
mwasif is offline Offline
312 posts
since Dec 2007
Permalink
May 7th, 2008
0

Re: MySQL Error 1064 when DELETEing

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

Query:
MySQL Syntax (Toggle Plain Text)
  1. DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;
  2. DELETE FROM images WHERE image_id = 804 AND plant_num = 2277 LIMIT 1;
  3. DELETE FROM images WHERE image_id = 805 AND plant_num = 2277 LIMIT 1;

Error:
MySQL Syntax (Toggle Plain Text)
  1. Could NOT UPDATE the database:
  2. 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:
MySQL Syntax (Toggle Plain Text)
  1. foreach($removals as $id)
  2. 	$sql .= "\nDELETE FROM images WHERE image_id = ".$id." AND plant_num = ".$_REQUEST['pid']." LIMIT 1;";
Last edited by ray_broome; May 7th, 2008 at 4:49 pm.
Reputation Points: 10
Solved Threads: 0
Light Poster
ray_broome is offline Offline
25 posts
since Aug 2004
Permalink
May 7th, 2008
0

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
MySQL Syntax (Toggle Plain Text)
  1. DESCRIBE images
would help us help you.
Reputation Points: 18
Solved Threads: 20
Junior Poster
trudge is offline Offline
176 posts
since Sep 2007
Permalink
May 7th, 2008
0

Re: MySQL Error 1064 when DELETEing

well the query i showed you is what prints out.
MySQL Syntax (Toggle Plain Text)
  1. DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;
  2. DELETE FROM images WHERE image_id = 804 AND plant_num = 2277 LIMIT 1;
  3. DELETE FROM images WHERE image_id = 805 AND plant_num = 2277 LIMIT 1;

and describe images is:
MySQL Syntax (Toggle Plain Text)
  1. FIELD 	       Type 	                   NULL 	Key 	DEFAULT 	Extra
  2. image_id          INT(10) UNSIGNED 	   NO 	PRI 	NULL 	AUTO_INCREMENT
  3. plant_num        INT(10) UNSIGNED 	   NO 	MUL 	NULL 	 
  4. image_src 	       VARCHAR(255) 	   NO 	  	NULL 	 
  5. image_alt 	       VARCHAR(64) 	   NO 	  	NULL 	 
  6. location_taken  VARCHAR(64) 	   YES 	  	Barbados 	 
  7. is_vouchered    TINYINT(1) 	   NO 	  	0 	 
  8. is_best_fit 	       TINYINT(1) 	   NO 	  	0
Last edited by ray_broome; May 7th, 2008 at 5:39 pm.
Reputation Points: 10
Solved Threads: 0
Light Poster
ray_broome is offline Offline
25 posts
since Aug 2004
Permalink
May 7th, 2008
0

Re: MySQL Error 1064 when DELETEing

What does
MySQL Syntax (Toggle Plain Text)
  1. 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.
Reputation Points: 18
Solved Threads: 20
Junior Poster
trudge is offline Offline
176 posts
since Sep 2007
Permalink
May 7th, 2008
0

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
Reputation Points: 10
Solved Threads: 0
Light Poster
ray_broome is offline Offline
25 posts
since Aug 2004
Permalink
May 7th, 2008
0

Re: MySQL Error 1064 when DELETEing

Can you execute
MySQL Syntax (Toggle Plain Text)
  1. 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.
Last edited by trudge; May 7th, 2008 at 7:33 pm.
Reputation Points: 18
Solved Threads: 20
Junior Poster
trudge is offline Offline
176 posts
since Sep 2007
Permalink
May 7th, 2008
0

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:
MySQL Syntax (Toggle Plain Text)
  1. $sql .= "\nDELETE FROM images WHERE plant_num = ".$_REQUEST['pid']." AND image_id IN (".implode(",",$removals).");";

query
MySQL Syntax (Toggle Plain Text)
  1. 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
Last edited by ray_broome; May 7th, 2008 at 8:04 pm.
Reputation Points: 10
Solved Threads: 0
Light Poster
ray_broome is offline Offline
25 posts
since Aug 2004
Permalink
May 7th, 2008
0

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.
Reputation Points: 18
Solved Threads: 20
Junior Poster
trudge is offline Offline
176 posts
since Sep 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: Insert doesn't do what it's supposed to
Next Thread in MySQL Forum Timeline: mySQL TRIGGER problem





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC