SQL syntax error generated by phpMyAdmin when updating field

Question

I need to change some domain name into a table containing e-mail addresses (primary key).
I tried to extract (with a select) the fields that I need to update:

SELECT `Email`, CONCAT( 
    SUBSTRING_INDEX(`Email`, '@olddomain.', 1),
    '@newdomain.', 
    SUBSTRING_INDEX(`Email`, '@olddomain.', -1)) AS NewMail
FROM `mailaddresses` 
WHERE INSTR(`Email`,'@olddomain.')>0

This works fine and gives me old and new e-mails.
Follows an example of the returned data:

   EMail               NewMail
123@olddomain.com   123@newdomain.com
456@olddomain.com   456@newdomain.com
789@olddomain.com   789@newdomain.com

So I tried to update using the following query:

UPDATE `mailaddresses` 
    SET `Email` = CONCAT( 
        SUBSTRING_INDEX(`Email`, '@olddomain.', 1),
        '@newdomain.', 
        SUBSTRING_INDEX(`Email`, '@olddomain.', -1)) 
WHERE INSTR(`Email`, '@olddomain.')>0

But phpMyAdmin is saying that there's an SQL syntax 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 '1) '@newdomain.' SUBSTRING_INDEX(Email'@olddomain.', )) FROM mailaddresses WHER' at line 2

I noticed that two commas ',' are missing in the error message so I think phpMyAdmin is doing something nasty there.

EDIT:
I realized that my SQL code is fully working because I tested it on a MySQL console and it's working fine. I think I have problems with PhpMyAdmin (I don't know what kind of problems though).

Here some data from my Xampp/PhpMyAdmin home page:

Database server
Server: 127.0.0.1 via TCP/IP
Server type: MySQL
Server version: 5.6.24 - MySQL Community Server (GPL)
Protocol version: 10
Web server
Apache/2.4.12 (Win32) OpenSSL/1.0.1l PHP/5.6.8
Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $
PHP extension: mysqli
phpMyAdmin
Version information: 4.3.11

I'm sure of this because I ran my SQL code from vb.net and it worked.

So my questions are:
Is there anyone who has had the same problem?
How can I solve this?

it seems this field has an unique index and after such changes you are getting duplicate records. so share error details so that it can be checked. — Zafar Malik, Feb 02 '16 at 07:50
I do not know where your syntax error comes from but it seems you are trying to re-implement the replace-Function of mysql. Maybe better use that instead: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace — Martin Schneider, Feb 02 '16 at 07:58
@OP: Why did you use `@xxxdomain` as *literals* ??? Aren't they session variables??? — Ravinder Reddy, Feb 02 '16 at 08:00
@OP: And it seems you have posted a different query statement that does not show the partial statement as seen in error statement ... — Ravinder Reddy, Feb 02 '16 at 08:02
@RavinderReddy I posted what phpMyAdmin returns to me. '@' is a char of the mail address, but even if I cut off it, I get syntax error — genespos, Feb 02 '16 at 08:04
@genespos: Observe error statement keenly.... Some *comma*s are missing .... — Ravinder Reddy, Feb 02 '16 at 08:06
@RavinderReddy I already noted it. I wrote it into my question. But 'commas' are missing only into the error message not in my query!!! — genespos, Feb 02 '16 at 08:08
Can you post a existing sample `email` field value and the target value? — Ravinder Reddy, Feb 02 '16 at 08:09
If you think it's phpMyAdmin post its version. Have you tried running the same query on the MySQL shell? Still getting the error? Have you tried updating phpMyAdmin? — Francesco Casula, Feb 02 '16 at 10:28
@FrancescoCasula I added version data. I tryed to run query from xampp shell and it worked — genespos, Feb 02 '16 at 11:03
The latest version is 4.5.4.1. I suggest you try to update it. You can also try to run your query on a single PHP script file than you can run from the CLI just to see if you're getting the same error also there. — Francesco Casula, Feb 02 '16 at 11:21
@FrancescoCasula I Updated to 4.5.4.1 and now it works. Thanks. If You'll post it as answer I'll accept — genespos, Feb 02 '16 at 11:50

score 0 · Answer 1 · answered Feb 02 '16 at 08:25

You can use REPLACE(str,from_str,to_str) string function to change part of any field value...

UPDATE mailaddresses 
   SET email = REPLACE( email, '@olddomain.', '@newdomain' )
 WHERE INSTR( email, '@olddomain.' ) > 0

Example:

mysql> set @olddomain = '@google.com';
Query OK, 0 rows affected (0.00 sec)

mysql> set @newdomain = '@gmail.com';
Query OK, 0 rows affected (0.00 sec)

mysql> set @email = 'rr.arepally@google.com';
Query OK, 0 rows affected (0.00 sec)

mysql> select replace( @email, @olddomain, @newdomain ) as modified_email;
+-----------------------+
| modified_email        |
+-----------------------+
| rr.arepally@gmail.com |
+-----------------------+
1 row in set (0.00 sec)

mysql>

I don't understand what's happening: I get syntax error and again into the error message commas are missing — genespos, Feb 02 '16 at 08:32

Francesco Casula · Accepted Answer · 2016-02-02T14:12:42.363

0

It looks from your question that you're using an outdated version of phpMyAdmin plus the very same query apparently works when ran on the MySQL console itself. I therefore suggest you update phpMyAdmin to its latest version. At the moment of writing is the v4.5.4.1.

Here the changelogs.

edited Feb 02 '16 at 14:12

answered Feb 02 '16 at 13:25

Francesco Casula

26,184
15
132
131

SQL syntax error generated by phpMyAdmin when updating field

2 Answers2