shuvrow shuvrow - 7 months ago 28
SQL Question

mysql update query throwing syntax error

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

you don't should use from in update because the updating table is already define in 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%'