SQL, update a column with values from another table

I’m always forgetting and re-learning this one. Why won’t it stick? Now it’s here as an aide-memoire. How to cross-update a field from values in another table.

The scenario is: you have an empty column in one table which you want to update with related values in another table. How to choose the values? Well, in my case, they are dependent on a different column in the first table. Typically, I sometimes find myself importing a table from a sheet, and then starting to normalise it by re-presenting some columns with numbered IDs instead of their original (inefficient) strings. So temporarily the string remains in the first table, side by side with the column which will be the normalised ID (foreign key into the second table).

Anyway, here’s the syntax

update tbl_nz_schools set `authority id` = 
 (select id from tbl_nz_school_authorities WHERE
  tbl_nz_schools.authority = tbl_nz_school_authorities.name);

After that, you can remove the string column and just rely on the ID. (Oh, just eyeball the data before you do so! You’ll be stuck if you find a mistake after you removed the matching column).