I've got an old customer table (customer_old), which needs to be partially transferred to a new one. In the old one the house number had its own column (customer_old.entry_house_number). In the new one it is part of the street column (customer_address.street). Also there are now two tables. One for the customers (customer) and one for the customers addresses (customer_address), as one customer can have multiple addresses.
table: customer_old
+-------------------------+--------------------+--------------------+
| customers_email_address | entry_street | entry_house_number |
+-------------------------+--------------------+--------------------+
| customer1@domain.com | A street | 23 |
| customer2@domain.com | Another street | 63a |
| customer3@domain.com | Yet another street | No. 4 |
+-------------------------+--------------------+--------------------+
|
table: customer |
+-------+----------------------+ |
| id | email | |
+-------+----------------------+ |
| 12345 | customer1@domain.com | |
| 23456 | customer2@domain.com | |
| 34567 | customer3@domain.com | | Append
+-------+----------------------+ |
|
table: customer_address |
+-------------+---------------------------+ |
| customer_id | street | |
+-------------+---------------------------+ |
| 12345 | A street already modified | |
| 23456 | Another street | <----------|
| 34567 | Yet another street |
+-------------+---------------------------+
I'm using a join on the customers email to connect the old and the new customer tables and a join on the customer id to get its addresses. Then I check, if the street address is still the same as in the old table, as some of the addresses have already been modified (see WHERE). These rows can be ignored for the update. I can get the concerned rows using the following select query:
SELECT customer_address.street, customer_old.entry_house_number
FROM customer # New table
INNER JOIN customer_old ON customer.email = customer_old.customers_email_address # Join by email
INNER JOIN customer_address ON customer.id = customer_address.customer_id # Join by id
WHERE customer_address.street = customer_old.entry_street_address; # Only unchanged ones
Now I need to update customer_address.street to a concatenated value based on customer_address.street, a whitespace and customer_old.entry_house_number (e.g. 'Another street' and '63a' to 'Another street 63a'). How can I achieve that?
I've found update queries using joins (e.g. mySQL update column with concat of another table), but non with multiple inner joins, where another table is used as 'bridge'. This is what I tried so far, but it doesn't seem to be correct syntax.
UPDATE customer_address
INNER JOIN customer_old ON customer.email = customer_old.customers_email_address
INNER JOIN customer_address ON customer.id = customer_address.customer_id
SET customer_address.street = CONCAT(customer_address.street, '', customer_old.entry_house_number)
WHERE customer_address.street = customer_old.entry_street_address;