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.

DaleSure, read the sticky at the top and supply the info we will need to help you...|||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.sql