Tricky SQL Problem

warrenaldo

Folks,

I have a tricky one.

A Customer table - with thousands of records. Fields - Cust_ID, Cust_Name.
An Order Table I imported. It contains order details and a Customer_Name.

I dont want the Customer_Name in Orders. I want Customer_ID. A FK relationship.

I need to preform some lookup to replace the Customer_Name with the Cust_ID.

Any help?

seamus

What server?

warrenaldo

SQL Server

seamus

First thing I would do is add in an additional column for the customer ID, so you don't go overwriting the name and getting all mixed up.

So
ALTER TABLE [Order] ADD COLUMN [Customer_ID] INT NULL

Then you do an update into this column based on the customer name:

UPDATE Order
SET [Order].[Customer_ID] = [Customer].[Customer_ID]
FROM [Order], [Customer]
WHERE
[Order].[Customer_Name] = [Customer].[Customer_Name]

The syntax might be a bit off, but it should work.

Then you get the rows where there were no matches:

SELECT * FROM [Order] WHERE [Customer_ID] IS NULL

And hopefully there are only a few, so you can correct them manually.

Then you drop the Customer_Name column from the order table, set the Cust_ID column to NOT NULL and create your FK constraint.

warrenaldo

My god - its so handy when you put it like that - I was way over complicating it.

Using replace function instead of adding in extra column.

Thats fantastic. Thanks.

Seth Brundle

Be aware that two customers with the same name (assuming they are not unique) can screw this up but given what you have there isn't much you can do about it!