1

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?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Zonker.in.Geneva
  • 1,389
  • 11
  • 19
  • 1
    I would not concatenate the fields like that. Suppose field F1 contains 'abc' and F2 contains 'def' and you're searching for 'cd', then your query would record a hit, whereas the value 'cd' doesn't occur in either field. Better use `CONCAT_WS()` and separate the fields with a space. I also hope you've set the value of FILTER just before you use this query, it is a very unusual method. – KIKO Software Sep 22 '18 at 07:54
  • As to your question, we cannot answer it because the code is missing. However, if I had to guess I would say you forget the space between the zero and WHERE: something like this: `SET FILTER = 0WHERE`. (Don't you like spaces?) – KIKO Software Sep 22 '18 at 07:56
  • ?! the code is right there in my post. again, the code works perfectly on my local dev, so it's not a syntax error like a missing space. The same code, unchanged, fails on live site. It's a problem with MariaDB. – Zonker.in.Geneva Sep 22 '18 at 07:58
  • Sorry, I was too vague, I meant the code you use to access the database. It could be PHP code, or any other language. Or are you using pure SQL? – KIKO Software Sep 22 '18 at 08:00
  • 1
    @KIKOSoftware I'm concatenating because I found several posts that use this method. But, I'm not asking for comments about the method. I'm asking why the SAME SQL statement succeeds with MySQL but fails with MariaDB. I will, however, change it to `CONCAT_WS` to avoid the false positives you mentioned. – Zonker.in.Geneva Sep 22 '18 at 08:01
  • Comments can be used to comment on the question, what you want is an answer. – KIKO Software Sep 22 '18 at 08:02
  • @KIKOSoftware the rest of the code to connect, etc. works fine. The error message doesn't say "Can't connect." it says "syntax error." – Zonker.in.Geneva Sep 22 '18 at 08:03
  • MariaDB probably doesn't support that syntax. – Shawn Sep 22 '18 at 08:04
  • @Shawn It does support `UPDATE...SELECT` and it's supposed to be a drop-in replacement for MySQL. Clearly, it's not. And I'm trying to figure out what needs to change. – Zonker.in.Geneva Sep 22 '18 at 08:05
  • @KIKOSoftware Yes, I have many spaces all over the place; I'm sure you can see them. I typed in the SQL statement and then used PHPMyAdmin's format command to pretty it up. Spacing is not the issue. – Zonker.in.Geneva Sep 22 '18 at 08:06
  • Maria started as a fork of My, but they've diverged in the years since. You should consult the MariaDB documentation to see if it supports subqueries that aren't selecting from a table like you're trying to use. – Shawn Sep 22 '18 at 08:08
  • I can understand your frustration. What happens if you tried the above query in PHPMyAdmin on MariaDB, does it work, or show an error? (you might already be doing this, but we don't know) – KIKO Software Sep 22 '18 at 08:08
  • Why are you even using IN for that, anyways? It seems needlessly over complicated. – Shawn Sep 22 '18 at 08:10
  • @KIKOSoftware Using SequelPro, PHPMyAdmin, and my PHP script, I get the same error. – Zonker.in.Geneva Sep 22 '18 at 08:10
  • @Shawn the select will match a bunch of IDs. How else would I change the `filter` for those IDs if not with `IN`? – Zonker.in.Geneva Sep 22 '18 at 08:11
  • 1
    `UPDATE mapping SET filter = 0 WHERE concat(blah,blah) NOT LIKE 'whatever'` would make more sense, wouldn't it? – Shawn Sep 22 '18 at 08:11
  • If that's the case then Shawn might be right: MariaDB doesn't support this syntax. – KIKO Software Sep 22 '18 at 08:12
  • How is the subquery supposed to work without a `FROM` clause? Where is it selecting from? – Barmar Sep 22 '18 at 08:19
  • @Barmar yeah, I was confused by that, too. When I first wrote it *with* the FROM clause, it failed. Turns out, if the subquery references the same table, you don't restate the FROM clause. – Zonker.in.Geneva Sep 22 '18 at 08:22
  • @Shawn THAT WORKED! I removed the `ID in (SELECT...` and it works. Thank you! – Zonker.in.Geneva Sep 22 '18 at 08:23
  • @Zonker.in.Geneva The solution to that is here: https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Barmar Sep 22 '18 at 08:23
  • @Barmar But....that's a solution to a problem I'm not having. – Zonker.in.Geneva Sep 22 '18 at 08:25
  • It kind of is. You first got that error, then you tried to solve it by removing `FROM`, but that's not the correct way. But Shawn's query is best for what you're actually trying to do. – Barmar Sep 22 '18 at 08:27
  • You have too many inconsistencies -- `from mapping` missing from SQL, different column names, etc. _Fix the SQL_! – Rick James Oct 07 '18 at 03:28
  • @RickJames Yeah, no. I researched this before posting, you realize. I searched the documentation and in fact, the `from mapping` caused the statement to fail, which is why it's not there. Again, it WORKS with MySQL on local dev and FAILS with MariaDB. It's not the SQL, it's the database and I'm trying to find out why. Thanks for playing, though. – Zonker.in.Geneva Oct 08 '18 at 06:36

1 Answers1

2

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • It's a wonder OP's first `UPDATE` query even worked as *id* is not a scalar and concat fields would not exist! I think OP mistyped queries leaving out the `FROM` between MySQL to MariaDB. – Parfait Sep 22 '18 at 15:44