Left join plus replace from right table

[fboehlandt]

Hi everyone

I have two tables which I join using a LEFT JOIN statement. Table1 contains more entries then Table2. All unique entries in Table2 also appear in Table1. Thus the JOIN statement is as follows:

FROM (Table1 LEFT JOIN Table2 on Table1.IDCode = Table2.IDCode)

There are a few fields in Table2 that should always overwrite corresponding entries in Table 1. To give an example:

Table1
[IDCode] [CustomerName] [Address] [Phone]
[1001] [John Doe] [Main Rd 1] [555-1234] [j.doe@net.com]
[1002] [Jane Doe] [Oak Ln 2] [555-9876] [jane@email.net]

Table2
[IDCode] [Phone]
[1002] [[COLOR="Red"]555-6789[/COLOR]] [jane@email.net]

IDCode is the primary key. Note that in my example Table1 contains the comprehensive list of customers whereas not all customers appear in Table2. However, we assume that phone number as recorded in table 2 is correct and should overwrite the phone number in Table1.

Lastly, this is part of a database clean-up. I realise that the above is a bad design but Table2 will be removed once the query has run.

Any help is greatly appreciated

Best regards

P.S. the final table should look as follows

[B]Table1[/B]
[IDCode] [CustomerName] [Address] [Phone]
[1001] [John Doe] [Main Rd 1] [555-1234] [j.doe@net.com]
[1002] [Jane Doe] [Oak Ln 2] [[COLOR="red"]555-6789[/COLOR]] [jane@email.net]

 

[isladogs]

Morning

Deja vu all over again as the saying goes

Change the update query to inner join like this

UPDATE Table1 INNER JOIN Table2 ON Table1.IDCode = Table2.IDCode SET Table1.Phone = [Table2].[Phone];