MySql update syntax error from inner join

Question

UPDATE
    `universities`
SET
    `universities`.countryid = `countries`.id,
FROM
    `universities`
INNER JOIN
    `countries`
ON
    `universities`.country = `countries`.name

When I try to run the sql statements above via PhpMyAdmin, it would give syntax errors. I wrote the statements based on this answer.

Gordon Linoff · Accepted Answer · 2015-04-06 20:21:50Z

12

This is the correct syntax in MySQL:

UPDATE universities u JOIN
       countries c
       ON u.country = c.name
    SET u.countryid = c.id;

In addition, I introduced table aliases (so the query is easier to write and to read) and removed an extraneous comma.

answered Apr 6, 2015 at 20:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

George Rappel Over a year ago

Weird how this one works but many other with a lot of upvotes don't.

Anthony Kal Over a year ago

because the other one is using mariadb, meanwhile you are using mysql.

Collectives™ on Stack Overflow

MySql update syntax error from inner join

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related