Error in Mysql query with LIKE %...%

Question

I am trying to update a table using another table. I am not able to determine how to use LIKE %…% feature here. Please help me out.

Table1:

TableToBeUpdated:

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.

Table2:

TableToCheckFrom :

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

The result should be:

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.

——

I am using this formula but its giving me constant error:

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

Thanks in Advance!

score 1 · Accepted Answer · answered Apr 23 '16 at 08:58

1

You need to put the % in quotes and concatenate them to the keyword. And you need to join the two tables so you can refer to columns from both of them.

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

answered Apr 23 '16 at 08:58

Barmar

741,623
53
500
612

1. Perfect!! 2.Genious!! Worked flawlessly! - Thank You! – AddyTiger Apr 23 '16 at 09:08
what if I want to include this in your query: WHERE TableToBeUpdated.location IS NOT NULL – AddyTiger Apr 23 '16 at 10:14
1

Put that at the end of the query. – Barmar Apr 23 '16 at 10:14

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

You query is wrong (you never tell the DBM that you want to access the TableToCheckFrom table). For an idea on how write such queries look at this question Update mysql table with data from another table

If you find the like %% as an additional difficult strip it: first prepare a working query with a simply condition and then add the like one.

edited May 23 '17 at 11:59

Community

1
1

answered Apr 23 '16 at 08:54

Dario Corsetti

76
3

Error in Mysql query with LIKE %...%

2 Answers2