Update value where values from two columns match one column value?

user_3505865 0
Viewed 33

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!

mysql
1 Answers

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  |
user_104988280
Related