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?

Answer 1

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%'