MySQL Update error with select

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 - http://www.mysqltutorial.org/mysql-update-join/ — Joe C, Jan 24 '18 at 15:52

score 1 · Accepted Answer · answered Jan 24 '18 at 15:55

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 '18 at 15:55

Matt Spinks

6,380
3
28
47

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. – Pankaj Jan 24 '18 at 15:56

score 0 · Answer 2 · answered Jan 24 '18 at 15:54

0

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

answered Jan 24 '18 at 15:54

itdoesntwork

1,745
16
31

This answer lacks an explanation. – Ivan86 Jan 24 '18 at 21:11

MySQL Update error with select

2 Answers2