mysql update query throwing syntax error

Question

I am trying to update user information (phone number) but mysql showing syntax error.

Here is the query for showing all data (phone number)

select distinct a.phone
from users u
join updated_phone a on a.phone like concat(u.phone_no, '%')
where u.phone_no like '88%'

This query works fine.

Here is the query for updating phone number in users table

UPDATE u
SET u.phone_no=a.phone
FROM      updated_phone as a 
JOIN      users as u
         on a.phone like concat(u.phone_no, '%')
         where u.phone_no like '88%'

But this update query throwing error in syntax near FROM and not updating values. How to solve this syntax error and update information?

score 2 · Accepted Answer · edited May 04 '22 at 13:09

2

You shouldn't use from in update because the updating table is already defined in the UPDATE clause.

 UPDATE users as u
 JOIN   updated_phone as a    on a.phone like 'u.phone_no%'
 SET u.phone_no= a.phone
 where u.phone_no like '88%'

edited May 04 '22 at 13:09

Dharman

26,923
21
73
125

answered May 02 '16 at 08:44

ScaisEdge

129,293
10
87
97

I have update the answer .. with join before set and i have also changed the like concat – ScaisEdge May 02 '16 at 08:55
I tried it before ,query executed but not updating values. For better understanding my question is related to this post http://stackoverflow.com/questions/36953942/how-to-use-regexp-on-the-results-of-a-sub-query – Md Rashedul Hoque Bhuiyan May 02 '16 at 09:02
You want match the phone number but not if begin with '88' ... i have update the answer ..adding '%u.phone_no%' – ScaisEdge May 02 '16 at 09:14
Any way the sintax error is solved then the question you posted is solved the rest is be related to another question – ScaisEdge May 02 '16 at 09:15
Code i wrote in question is okay. I provided the link to understand what i really want. Error is gone but i am not getting desired output yet. Please have a look what makes this query return without updating values. – Md Rashedul Hoque Bhuiyan May 02 '16 at 10:18
I think your previous answer is correct . Please fix it and i'll mark it as completed :) – Md Rashedul Hoque Bhuiyan May 02 '16 at 10:27
I have update the answer .. restoring the previous answer ..thank – ScaisEdge May 02 '16 at 11:07

mysql update query throwing syntax error

1 Answers1