How to pull data from one table into another using a column value as reference-CodePudding

I am completely new to MySql with absolutely zero experience with databases so forgive me if the question is not phrased exactly right or if I have missed anything out.

I have two tables, the first contains customer information that they (the customer) have entered via a web form, and the second contains address information provided by the post office. Both tables contain a postcode/zip code.

Customer Table (1) [contains 50,000 records]

customer_name	customer_address	customer_postcode	new_column1	new_column2
Fred Bloggs	AnyRoadwitherrors	BH21 7DT	null	null
Mary Smith	Sxtreetname Xyz	LE3 5SG	null	null
George Brown	1a Hightreet London	SW21 2EE	null	null

** The customer table often contains spelling or other errors in the address column and does not contain the town/city but the postcode is always accurate.

Address Table (2) [contains 23,000,000 records]

postcode	street_address	town_address
SW21 2EE	High Street	London
BH21 7DT	Any Road	Bournemouth
LE3 5SG	Street Name	Leicester

*The address table provided by the post office contains accurate information.

I would like to insert data from Table 2 into Table 1 using the customer_postcode column as a reference.

Customer Table (3)

customer_name	customer_address	customer_postcode	new_column1	new_column2
Fred Bloggs	AnyRoadwitherrors	BH21 7DT	Any Road	Bournemouth
Mary Smith	Streetname Xyz	LE3 5SG	Street Name	Leicester
George Brown	1 HighStreet London	SW21 2EE	High Street	London

I've tried various methods using "insert into" and "inner join", all without success and having spent days on stack overflow and google searching for answers I am now stuck and so thought I would ask those who know significantly more than I do for assistance.

CodePudding user response：

I hope I got all the column names correct

UPDATE customer1 c, postcodes po
SET c.new_col1 = po.street_address,
    c.new_col2 = po.town_address
WHERE c.customer_postcode = po.postcode;

DEMO