5
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,870
  • 30
  • 89
  • 134

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786