Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: SQL Statement Woes

    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.

  2. #2
    Join Date
    Mar 2004
    Location
    Karachi, Pakistan
    Posts
    3
    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....

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •