MySQL: Escaping backslash in the LIKE statement works in SELECT query but does not work in UPDATE query

When I run the following query:

SELECT * FROM notifications WHERE `object_type` LIKE 'App\\\\Models\\\\%';

it works fine.

But when I run the following query:

UPDATE `sss_notifications` SET `object_type`=CONCAT('App\Models\', object_type) WHERE `object_type` NOT LIKE 'App\\\\Models\\\\%';

I get the following error:

ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.

What am I doing wrong?

debiprasad

3 years ago

Best Answer

Level 2

The backslash before the single quote in the CONCAT function escapes the single quote. So, it was parsing the query as something else and was throwing the error. By escaping the backslash before the single quote fixed it.

UPDATE `sss_notifications` SET `object_type`=CONCAT('App\\Models\\', object_type) WHERE `object_type` NOT LIKE 'App\\\\Models\\\\%';

I also needed to escape the other backslash after 'App' in the CONCAT function.

Please or to participate in this conversation.