Update Query - Update Table with Values from Another Table

[minifelt]

I have 2 tables: SALES table and CUSTOMER table.

The SALES table contains inconsistent customer name. I need to assign a key to the customer in the SALES table based on the CUSTOMER table.

I would like to update the SALES.customerID field = to the CUSTOMER.customerID field. I wrote this query but it doesn't work because the subquery is returning more than 1 value.

update CUSTOMER
set customerID=(select customerID from CUSTOMER)
where CUSTOMER.CustomerName=(select CustomerName from CUSTOMER)

To explain further, the Customer table contains customer names with the same customer ID. Example:

CustomerName CustomerID
ABC 1
ABC Inc 1
ABC, Inc. 1
Atlas Inc. 2
Atlas Corp, Inc. 2

The Sales table customer name is entered many different ways for the same customer.

Please help... Thank you very much.

 

[SQLBill]

You need to relook at your query. You aren't updating anything in SALES. Every part of your query uses the CUSTOMER table.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

I'll also add this example from the Books OnLine:

Use the UPDATE statement using information from another table
This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table.

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)[/quote BOL]

You can find the BOL from Query Analyzer by clicking on Help or going to Start>Programs>Microsoft SQL Server 2000>Books OnLine. I selected the Index tab and looked up UPDATE.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLSister]

Also the BOL example is out of date. Write your update using the ansi join syntax
something like (untested):

update s
set customerId = c.customerid
from sales s
join 
customer c on c.customername = s.customername

Now be very careful with this as multiple people/businesses have the same name. If possible I would also link to a table that stores addresses or phone numbers or something else that will help you identify the person as the same person or the business as the same business. Further, if the names are inconsistent now, you may not be able to directly match up all the names in the sales table to the customer table. In this case these records will have to be identified and someone will have to manually try to figure out that Joe Smyth in the sales table is really Joe Smith in the customer table. This is a data integrity issue and it's good that you are addressing it, but fixing the issue is likely to be timeconsuming and extremely painful becasue there are likely many many records which are not a direct match. Eventually you may have to accept that there are some you cannot reliably match. Good luck as you will need it.



"NOTHING is more important in a database than integrity." ESquared

 

[minifelt]

SQLSister, thank you so much for the input. The update statement works. You understand exactly what I'm upgainst - troublesome dataintegrety issu. Yes, I truly need the GOOD LUCK!... God bless you

Thank you SQLBill for providing a different approach. This works too.