AddyTiger AddyTiger - 7 months ago 8
SQL Question

Error in Mysql query with LIKE %...%

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!

Answer

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