mysql - Mysql 查询中出现 LIKE %...% 错误

Question

我正在尝试使用另一个表更新一个表。我无法确定如何在此处使用 LIKE %...% 功能。请帮帮我。

表1:

待更新表格:

id | location | value
------------
1  | california | I am going to be here soon.
2  | Nill | Hello I love playing chess and yes.
3  | Nill | my hotel room is just shitty!
4  | Nill | Why on earth God doesn’t live on earth!
5  | Nill | friends of friends and their dogs.

表2:

表格到检查来源:

uniqueid | location | keyword
---------------------
1        | Texas | Why on earth
2        | NewYork   | friends and their
3        |   Washington | love playing chess
4        | NewYork   | their dogs

结果应该是:

id | location | value
------------
1  | California | I am going to be here soon.
2  | Washington | Hello I love playing chess and yes.
3  | Nill | my hotel room is just shitty!
4  | Texas | Why on earth God doesn’t live on earth!
5  |NewYork| friends of friends and their dogs.

——

我正在使用这个公式，但它给了我持续的错误:

UPDATE TableToBeUpdated, TableToCheckFrom
SET TableToBeUpdated.location = TableToCheckFrom.Location
WHERE TableToBeUpdated.Value LIKE %TableToCheckFrom.Keyword%

提前致谢!

Answer 1

您需要将 % 放在引号中并将它们连接到关键字。您需要连接两个表，以便可以引用两个表中的列。

UPDATE TableToBeUpdated AS u
JOIN TableToCheckFrom AS c ON u.Value LIKE CONCAT('%', c.Keyword, '%')
SET u.location = c.Location