SQL UPDATE Table1 to Table 2

Question

I'm running into an issue with the SQL UPDATE command. I have two tables, Customer and Customer2. The Customer table contains all customer information. The Customer2 table contains customers who have outstanding invoices. What I am trying to do is update Customer2 table with the area code from the Customer table. The matching parts of the two tables is the column CUS_CODE.

Here's what I have so far:

UPDATE Customer2
SET Customer2.cus_areacode = Customer.cus_areacode
FROM Customer2
INNER JOIN Customer
ON (customer.cus_code = customer2.cus_code)
WHERE customer.cus_code = customer2.cus_code;

I'm getting a Syntax error starting at 'From Customer2'. I've been racking my brain trying to get this working, any help would be appreciated. I'm performing this task using SQL inside MS Access.

http://stackoverflow.com/questions/1375207/how-do-i-perform-update-query-with-subquery-in-access — Paul Abbott, Apr 25 '16 at 21:38

score 0 · Answer 1 · answered Apr 25 '16 at 21:37

0

You want to do something like this. The where clause is redundant as you are already joining on the columns.

update customer2 as c2 inner join customer as c on c2.[cus_code]=c.[cus_code]
set c2.[cus_areacode]=c.[cus_areacode]

answered Apr 25 '16 at 21:37

cableload

4,215
5
36
62

That worked like a charm and it makes sense. I was following instructions from other sites and the original I posted was cut-n-paste from sample code. This code you posted is much simpler. One question I do have is why use 'as' to convert Customer2 and Customer to C2 and C respectively? -Thank you! – Claus Apr 25 '16 at 22:01
How would this command change if I also wanted to update cus_phone from the same tables? – Claus Apr 25 '16 at 22:07

SQL UPDATE Table1 to Table 2

1 Answers1