CODEWITHSUNDEEP
mysqlsqlsql-updatemariadb
Zonker.in.Geneva
Zonker.in.Geneva

Reputation: 1499

MySQL vs. MariaDB - same update...select fails in MariaDB

I'm implementing a search on a simple database with about 7 relevant fields. The idea is to CONCAT the 7 fields and find the search term using the LIKE operator. Records that don't match have a flag (filter) that's set to zero.

UPDATE mapping SET FILTER = 0 WHERE id IN(
    SELECT id WHERE
      CONCAT(Field1, Field2, F3, F4, F5, F6, F7) NOT LIKE "%searchTerm%"
);

This works like a charm on my local dev with MySQL.

But, my host uses MariaDB and I get an error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE CONCAT( targets, organizations, ' at line 4.

I've isolated the SELECT statement (and added from mapping), and that works, too. But, the update only works locally, not on a live site.

I've been wracking my brains since last night. Any ideas?

Upvotes: 1

Views: 483

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You need to remove subquery because it is unnecessary:

-- works on MySQL/MariaDB
UPDATE mapping 
SET FILTER = 0 
WHERE CONCAT(F1, F2) NOT LIKE '%searchTerm%';

If you want to use IN operator as you proposed then you have to use FROM clause:

-- this will work only in MariaDB
UPDATE mapping 
SET FILTER = 0 
WHERE id IN(SELECT id FROM mapping WHERE CONCAT(F1, F2) NOT LIKE '%searchTerm%');

db<>fiddle demo Maria DB 10.3

And to help MySQL:

UPDATE mapping 
SET FILTER = 0 
WHERE id IN(SELECT id   -- note additional subquery
            FROM (SELECT id FROM mapping 
                  WHERE CONCAT(F1, F2) NOT LIKE '%searchTerm%') sub);

db<>fiddle demo - MySQL

Related article: You can't specify target table for update in FROM clause

Upvotes: 2

Related Questions