MYSQL: Using column name in CONCAT() function gives syntax error 1064

Question

I have this query

UPDATE `fitment_drums` SET `liters` = CONCAT(`liters`,'.0') WHERE `liters` LIKE '_'

Which results in 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 ''.0') FROM `fitment_drums` WHERE `liters` LIKE '_'' at line 1

When I substitute an ordinary string, eg. CONCAT ('asdf','.0') it works fine. I've tried using a select statement as an argument, and have also tried using a temporary table:

CREATE TEMPORARY TABLE t1 (SELECT * FROM `fitment_drums` WHERE liters like '_') 
UPDATE `fitment_drums` SET liters = CONCAT(t1.liters,'.0') where t1.id = id

You don't have a semicolon following your CREATE TABLE statement. — Bill Karwin
– Bill Karwin, Commented Nov 9, 2016 at 23:15
@grahamj42 Why would that matter for a syntax error? Why would it matter at all, since MySQL automatically converts numbers to strings when used with CONCAT()? — Barmar
– Barmar, Commented Nov 9, 2016 at 23:53
The error isn't coming from the query you showed. The error message says the query contains FROM `fitment_drums` , but that's not anywhere in that query. — Barmar
– Barmar, Commented Nov 9, 2016 at 23:56

qSort · Accepted Answer · 2016-11-10 00:28:17Z

1

Ok, so I found the "solution". I was running the query in simulation mode, which results in the error posted above. So I backed up the table and executed the query in live mode, and it worked. (for the record, I'm using phpMyAdmin)

Very strange.

Thanks to everyone who tried to help!

edited Nov 10, 2016 at 0:28

answered Nov 10, 2016 at 0:21

qSort

215 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2016-11-10 01:26:59Z

1

Since you revealed that you're using phpMyAdmin, you should know that temporary tables are dropped automatically as a database connection ends.

Each page view in phpMyAdmin is a separate PHP request, and thus a separate database connection.

So if you CREATE TEMPORARY TABLE in phpMyAdmin, then the temp table did not exist anymore by the time you ran your UPDATE.

answered Nov 10, 2016 at 1:26

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Collectives™ on Stack Overflow

MYSQL: Using column name in CONCAT() function gives syntax error 1064

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related