Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005

    Unanswered: Help Finding and Updating Dupes in 2 Tables

    Being fairly new to SQL and SQL scripting, I am at a loss on how to proceed on my issue.

    I have a MSDE database with 2 tables that need to modified. I am changing to a standard 12 digit code in my PATIENTS table for the field sChartCode nvarchar). That code will be in the form of 110012345678. 1100 will preceed the actual 8 digit chartcode

    In the PATIENTS table, the same person may be duplicated many times using vaiations such as 123456, 12345678, 012345678, 12345678 SMITH, 012345678 SMITH. For each of these records, they are linked to the RECORDS db using the field lPatientId (int).

    I have already manually updated about 20K records in the RECORDS db which
    takes way to many hours of time. New records will be imported at about 10K a week or so and will be over 100K soon. By the way, the SQL server is on the way.

    What I am looking for is an easier way to find the records that have not been
    converted in the PATIENTS db and see if they match one that has already been converted. If it has, it would need to update all records in the RECORDS db with the correct updated lPatientId and then delete the duplicate record(s) from the PATENTS db. If not, it would only need to add '1100...' to the lPatientId field.

    Any help or guidance that anybody can give will be most appreciated.


  2. #2
    Join Date
    Nov 2002
    Sure, read the sticky at the top and supply the info we will need to help you...

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2005
    Here is my file structure for both of the databases:

    Patients DB

    lPatientId] [int]
    sLastName] [nvarchar] (64)
    sFirstName] [nvarchar] (64)
    sMidName] [nvarchar] (64)
    sChartCode] [nvarchar] (64)
    lSex] [int]
    dtBirthDate] [float]
    sEthnicity] [nvarchar]
    fWeight] [float]
    sZipCode] [nvarchar]
    sAddress1] [nvarchar]
    sAddress2] [nvarchar]
    sHomePhone] [nvarchar]
    sWorkPhone] [nvarchar]
    sCellPhone] [nvarchar]
    sEMail] [nvarchar] (64)
    sFax] [nvarchar] (64)
    sComments] [nvarchar]

    Records DB

    lRecNo] [int]
    lPatientId] [int]
    lDoctorId] [int]
    lDiagId] [int]
    sEye] [nvarchar] (4)
    sPhotoCode] [nvarchar] (64)
    sProced] [nvarchar] (10)
    dtProcDate] [datetime]
    sImageType] [nvarchar]
    iDisk] [smallint]
    sSeriesInstanceUID] [nvarchar] (128)
    sStudyInstanceUID] [nvarchar] (128)
    sAccessionNumber] [nvarchar] (64)
    lRefPhysicianId] [int]
    lDbUniqueID] [int]
    lAcquisitionSystem] [int]
    lNumOfImages] [int]
    lInUse] [int]
    lPrevRecNo] [int]
    sDictation] [nvarchar] (255)
    lReviewed] [int]
    lDigitalReport] [int]
    lDicomSend] [int]
    dtLastUpdate] [datetime]
    lArchiveStatus] [int]
    lStudyId] [int]
    sSecPatientId] [nvarchar] (64)
    sSecPatientNameCode] [nvarchar] (64)
    lClinicId] [int]

    Since I am not sure of what I really need to do to get this to work, I haven't done much. What I do have is:

    update Patients
    set sChartCode = '11' + REPLICATE('0', 10-len(sChartCode)) + sChartCode
    where len(sChartCode) < 12

    This works great until it gets to a duplicate records and I can't get past that until I resolve the duplicate.

Posting Permissions

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