I am trying to update my mysql table (cityTable) with the cityFull value in administrativeTable where the cityCode values match country and city in cityTable.
I believe this statement should work. Only part that I am missing is what to put in the ???? for cityCode. It is a bit complicated as it also has a period (.)
UPDATE cityTable INNER JOIN administrativeTable ON (?????????? = administrativeTable.cityCode) SET cityTable.cityFull = administrativeTable.cityFull;
administrativeTable
+----------+-----------+
| cityCode | cityFull |
+----------+-----------+
| AU.Melb | Melbourne |
| NZ.Auck | Auckland |
+----------+-----------+
cityTable
+---------+------+-----------+
| country | city | cityFull |
+---------+------+-----------+
| AU | Melb | |
| NZ | Auck | |
+---------+------+-----------+
Cheers!
You need the function CONCAT():
UPDATE cityTable c
INNER JOIN administrativeTable a
ON CONCAT(c.country, '.', c.city) = a.cityCode
SET c.cityFull = a.cityFull;
See the demo.
Results:
| country | city | cityFull |
| ------- | ---- | --------- |
| AU | Melb | Melbourne |
| NZ | Auck | Auckland |