I have a single table with about 200,000 records. Each record refers to a single customer, but a customer may have one or more records. I need to identify which records belong to which customers and create a new field customer_id. So 1 or many records could end up with the same customer_id value. I can say that two or more records belong to the same customer if the values in fieldA match AND the values in fieldB match i.e. same customer if (rs1.fieldA = rs2.fieldA = rs[n].fieldA) AND (rs1.fieldB = rs2.fieldB = rs[n].fieldB)

For example, fields dob and ID are the fields I need to match to determine which records belong to which customers so

tool, dob, ID
mallet, 08/04/1959, AAAA
spanner, 08/04/1959, AAAA
hammer, 10/08/1965, AAAA
spade, 07/01/1955, AAAB
vice, 04/12/1980, AAAC

becomes

Cust ID, tool, dob, ID
00001, mallet, 08/04/1959, AAAA
00001, spanner, 08/04/1959, AAAA
00002, hammer, 10/08/1965, AAAA
00003, spade, 07/01/1955, AAAB
00004, vice, 04/12/1980, AAAC

I could write a nasty piece of code to do this but it would take for ever to run and I'm sure there is a wizzy piece of SQL that could do the job quicker! Any help would be greatly appreciated.

Hello wideblueyonder,

Well in my understanding what you want to do is add a new field named CustID ... right ???

if so then my next question is do you also want to make it Primary Key ??? because that wont be possible...

or are you going to make a Composite Key of CustID, DOB, ID ???

suppose you currently dont have a Primary Key defined.
Add a new column named CustID... after that you can simple Update the records by giving it the required condition.

for example...

Cust ID, tool, dob, ID
00001, mallet, 08/04/1959, AAAA
00001, spanner, 08/04/1959, AAAA
00002, hammer, 10/08/1965, AAAA
00003, spade, 07/01/1955, AAAB
00004, vice, 04/12/1980, AAAC

Update query for CustID=00001 can be

UPDATE tablename
SET CustID=@CustomerID
WHERE dob='08/04/1959'
AND ID='AAAA'

in the above mentioned query the "@CustomerID" is a Parameter that you can change similarly you can specify Parameters for the "dob" and "ID". if you want your query to be even more dynamic. but please do check out the format of the date etc.

Hope this helps.

Take care
Saqib....

The simplest solution would be to create a table with three columns: custid, date, and id. Select all of the unique combinations of date and id into this working table. Once you've isolated them, create new id values and assign them to this working table.

If you know which database engine you are using and how you want the new custId values formed, I could also provide an example.

-PatP