MySQL RSS MySQL RSS

MySQL Error 1064 when DELETEing

Thread Solved
1 2

•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

MySQL Error 1064 when DELETEing

 
0
  #1
May 7th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 190
Reputation: mwasif is an unknown quantity at this point 
Solved Threads: 25
mwasif mwasif is offline Offline
Junior Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #2
May 7th, 2008
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;
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #3
May 7th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 176
Reputation: trudge is an unknown quantity at this point 
Solved Threads: 20
trudge trudge is offline Offline
Junior Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #4
May 7th, 2008
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.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #5
May 7th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 176
Reputation: trudge is an unknown quantity at this point 
Solved Threads: 20
trudge trudge is offline Offline
Junior Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #6
May 7th, 2008
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.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #7
May 7th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 176
Reputation: trudge is an unknown quantity at this point 
Solved Threads: 20
trudge trudge is offline Offline
Junior Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #8
May 7th, 2008
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.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2004
Posts: 24
Reputation: ray_broome is an unknown quantity at this point 
Solved Threads: 0
ray_broome ray_broome is offline Offline
Newbie Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #9
May 7th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 176
Reputation: trudge is an unknown quantity at this point 
Solved Threads: 20
trudge trudge is offline Offline
Junior Poster

Re: MySQL Error 1064 when DELETEing

 
0
  #10
May 7th, 2008
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.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
Reply With Quote Quick reply to this message  
Reply
1 2

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the MySQL Forum
Thread Tools Search this Thread



agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm data database design developer development distinct drupal dui ec2 email enter enterprise eudora facebook form foss gartner gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opensource oracle php priceupdating query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC