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?

Dharman · Accepted Answer · 2022-05-04 13:09:22Z

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 4, 2022 at 13:09

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered May 2, 2016 at 8:44

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ScaisEdge Over a year ago

I have update the answer .. with join before set and i have also changed the like concat

Md Rashedul Hoque Bhuiyan Over a year ago

I tried it before ,query executed but not updating values. For better understanding my question is related to this post stackoverflow.com/questions/36953942/…

ScaisEdge Over a year ago

You want match the phone number but not if begin with '88' ... i have update the answer ..adding '%u.phone_no%'

ScaisEdge Over a year ago

Any way the sintax error is solved then the question you posted is solved the rest is be related to another question

Md Rashedul Hoque Bhuiyan Over a year ago

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.

|

Collectives™ on Stack Overflow

mysql update query throwing syntax error

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related