Mysql Error 1064 On Delete
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Can SELECT but can't DELETE up vote 0 down vote favorite I'm having difficulty deleting the desired rows from my table in MySQL. I'm using a rather complex subquery to select the rows, but for some reason I'm unable to delete them using a similar syntax. delete * from table1 as t1 where t1.col1 in (select y.col1 from table2 x join (select col1, col2 from table2 where col2 like "%- 2%") y on x.col2 = replace(y.col2, "- 2", "")); Again, I can select the exact rows I wanted deleted, but when I change the query to delete I get the following error: ERROR 1064 (42000): 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... Any help is greatly appreciated. mysql join subquery sql-delete share|improve this question edited Aug 11 '15 at 13:05 Brian Tompsett - 汤莱恩 3,091132775 asked Aug 18 '11 at 21:22 leadingzero 17118 add a comment| 4 Answers 4 active oldest votes up vote 2 down vote accepted Remove the * after DELETE. You don't usually delete individual columns, you delete entire rows. share|improve this answer answered Aug 18 '11 at 21:24 yan 15.5k12345 wow. so much time spent on such a dumb mistake. thanks all! –leadingzero Aug 18 '11 at 21:32 add a comment| up vote 2 down vote yep, you should use delete from table1 or if there are more than one table in the query, you need to enter the name of the table you want to delete from. delete table1 from table1 inner join table2 on table1.id = table2.t1ID share|improve this answer answered Aug 18 '11 at 21:28 Hudson Pena Magalhaes 762 add a comment| up vote 1 down vote Drop the *. A DELETE is an all or nothing kind of action. share|improve this answer answered Aug 18 '11 at 21:25 Joe 47.4k1084115 add a comment| up vote 1 down vot
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL error 1064, not sure http://stackoverflow.com/questions/7114313/can-select-but-cant-delete what I'm doing wrong here up vote -1 down vote favorite mysql -u username -p database -e deletedata.sql I get ERROR 1064 (42000) at line 1: 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 'deletedata.sql' at line 1 DELETE FROM 'table1' WHERE 'column' <= date_sub(current_date, INTERVAL 37 day); DELETE http://stackoverflow.com/questions/1337001/mysql-error-1064-not-sure-what-im-doing-wrong-here FROM 'table2' WHERE 'column' <= date_sub(current_date, INTERVAL 37 day); What could I be missing? mysql database mysql-error-1064 share|improve this question edited Feb 28 '10 at 19:59 OMG Ponies 199k37360417 asked Aug 26 '09 at 19:36 user163760 I tried both solutions and got the same errors...perplexing! –user163760 Aug 26 '09 at 20:00 Can you do a "describe 'table1'" from the MySQL console and paste your table schema here (or at least the relevant fields) so we can work directly with what you've got? –Brent Nash Aug 26 '09 at 20:45 add a comment| 3 Answers 3 active oldest votes up vote 0 down vote accepted -e executes a statement, not a sql file. What you need: mysql -u user -p pass db and then into the mysql cli: \. deletedata.sql Backslash dot means "source file" Or you can just pipe it: cat delete.sql | mysql -u user -p pass db share|improve this answer answered Aug 26 '09 at 20:33 K. Norbert 7,11523040 Thanks...the last think you said to try worked so I went back and looked at my syntax and checked the manual for mysql. This worked for me: mysql --
sure http://www.techjamaica.com/forums/showthread.php?60654-MySQL-Error-1064-when-DELETEing to check out the FAQ by clicking http://forums.mysql.com/read.php?10,506979 the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to mysql error visit from the selection below. Results 1 to 7 of 7 Thread: MySQL Error 1064 when DELETEing Thread Tools Show Printable Version May 7, 2008,01:08 PM #1 raymond View Profile View Forum Posts Newbie Join Date Jun 2005 mysql error 1064 Posts 28 Rep Power 0 MySQL Error 1064 when DELETEing I'm trying to carry out the following delete statements PHP Code: DELETEFROMimagesASiWHEREi.image_id=803ANDi.plant_num=2277LIMIT1
but i am getting the error:
DELETEFROMimagesASiWHEREi.image_id=804ANDi.plant_num=2277LIMIT1
DELETEFROMimagesASiWHEREi.image_id=805ANDi.plant_num=2277LIMIT1
Community Podcasts MySQL.com Downloads Documentation Section Menu: MySQL Forums :: Newbie :: MySQL Workbench 5.2.35: Error 1064: You have an error in your SQL syntax New Topic Advanced Search MySQL Workbench 5.2.35: Error 1064: You have an error in your SQL syntax Posted by: Renier Lewis () Date: December 29, 2011 01:42AM Hi Guys I have created tables in MySQL Workbench. When I now want to publish the scripts to an empty database, I get all sorts of errors. The errors are mostly related toExecuting SQL script in server ERROR: 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 ') NOT NULL AUTO_INCREMENT COMMENT 'This value uniquely identifies a specific cli' at line 2 Can anyone please assist as to what can be wrong with the script and why the Workbench would create a faulty script? CREATE TABLE IF NOT EXISTS `TTS`.`Client` ( `Client_ID` DOUBLE(11) NOT NULL AUTO_INCREMENT COMMENT 'This value uniquely identifies a specific client' , `Client_Name` VARCHAR(100) NOT NULL COMMENT 'The client name' , `Client_IsActive` TINYINT(1) NOT NULL DEFAULT TRUE COMMENT 'Identifies if a client is active.Overrides all settings, no completion of candidate actions should be possible if a client is deactivated' , PRIMARY KEY (`Client_ID`, `Client_Name`) , INDEX `fk_Client_Client_GUI1` (`Client_ID` ASC) , CONSTRAINT `fk_Client_Client_GUI1` FOREIGN KEY (`Client_ID` ) REFERENCES `TTS`.`Client_GUI` (`Client_ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1 COLLATE = latin1_swedish_ci; What am I doing wrong? Thanks Renier Navigate:Previous Message•Next Message Options:Reply•Quote Subject Written By Posted MySQL Workbench 5.2.35: Error 1064: You have an error in your SQL syntax Renier Lewis 12/2