3
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.

Community
  • 1
  • 1
Terry Li
  • 16,014
  • 29
  • 84
  • 132

1 Answers1

10

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.

Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709