MySQL Update error with select [duplicate]

Question

My below MySQL is giving error.

UPDATE P SET P.author_name = A.name 
FROM POSTS AS P INNER JOIN AUTHORS AS A
ON P.author_id = A.id;

You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'FROM POSTS AS P INNER JOIN AUTHORS AS A
ON P.author_id = A.id' at line 2

However, select works fine.

select P.id, a.name
FROM POSTS AS P INNER JOIN AUTHORS AS A
ON P.author_id = A.id;

I am not able to figure out what am I doing wrong, any help?

Your update looks correct for TSql. This link shows a different syntax for MySql - mysqltutorial.org/mysql-update-join — Joe C
– Joe C, Commented Jan 24, 2018 at 15:52

Matt Spinks · Accepted Answer · 2018-01-24 15:55:12Z

1

For MySql, you'll need to code the JOIN first, and then the SET statement:

UPDATE POSTS INNER JOIN AUTHORS ON POSTS.author_id = AUTHOR.id
SET POSTS.author_name = AUTHOR.name

answered Jan 24, 2018 at 15:55

Matt Spinks

6,7404 gold badges32 silver badges57 bronze badges

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

1 Comment

Pankaj Over a year ago

Thanks, it worked. I got it from the comment by Joe C. I will accept your answer when SO will allow me after 8 mins.

itdoesntwork · Accepted Answer · 2018-01-24 15:54:31Z

0

UPDATE POSTS 
INNER JOIN AUTHORS ON POSTS.author_id = AUTHORS.id;
SET POSTS.author_name = AUTHORS.name

answered Jan 24, 2018 at 15:54

itdoesntwork

1,90120 silver badges32 bronze badges

1 Comment

Ivan86 Over a year ago

This answer lacks an explanation.

Collectives™ on Stack Overflow

MySQL Update error with select [duplicate]

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related