Update issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update issue

Author

Topic

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-09-10 : 11:48:30

I have some data with mulitiple columns but the records are duplicate with the exception of two columns. I don't want to lose the information in either record but want to update the one record with the other. The first record has a drivers licence number and the second has a SSN. I need to update the first record with the second and delete the second record.

Existing data:
Col001 Col002 Col003
Jones CA1687280 NULL
Jones NULL 123-45-6789

Final result:
Col001 Col002 Col003
Jones CA1687280 123-45-6789

I have additional columns in the data set. This is just a sample. Any help would be appreaciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 12:03:25

something like:
update t1
set col003 = (select top 1 col002 from table1 where t1.col001 = col001) -- add here more conditions that make a duplicate
from table1
where col003 is null

delete table1
where col002 is null

Go with the flow & have fun! Else fight the flow

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-09-11 : 07:07:13

Thanks spirit1. I tried it but I am getting:

Col001 Col002 Col003
Jones CA1687280 CA1687280

I will work with it. This is a good start.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-11 : 08:10:27

yeah...
(select top 1 col002 from ...
should be
(select top 1 col003 from ...

Go with the flow & have fun! Else fight the flow

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-09-11 : 09:17:50

Thanks again. It works for the sample of just the two records. Now it writes the TOP 1 down the rest of the data:

Col001 Col002 Col003
Jones CA1687280 123-45-6789
Smith CA1687280 987-76-4321
Johnson CA1687280 947-76-4233

It's almost there. Thanks for the help.

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-09-12 : 14:43:21

This works but as cursors go it is very slow. I have about 1 million records to go threw. Any advice on how to do this without the cursor?

SET NOCOUNT on

DECLARE @getlname VARCHAR (50)
DECLARE @getfname VARCHAR (50)
DECLARE @getmname VARCHAR (50)
DECLARE @getdl VARCHAR (50)
DECLARE @getssn VARCHAR (50)

DECLARE dl_csr CURSOR FOR

SELECT distinct col006 from TABLE1 where col006 is not null

OPEN dl_csr
FETCH dl_csr INTO @getdl

WHILE @@fetch_status = 0
BEGIN
SET @getlname = (SELECT TOP 1 col001 FROM TABLE1 where col006 = @getdl)
SET @getfname = (SELECT TOP 1 col002 FROM TABLE1 where col006 = @getdl)
SET @getmname = (SELECT TOP 1 col003 FROM TABLE1 where col006 = @getdl)
SET @getssn = (SELECT TOP 1 col007 from TABLE1 where col007 is not null and col001 = @getlname and col002 = @getfname and col003 = @getmname)
update TABLE1 set col007 = @getssn where col007 is null and col001 = @getlname and col002 = @getfname and col003 = @getmname and col006 = @getdl

FETCH dl_csr INTO @getdl
END

CLOSE dl_csr
DEALLOCATE dl_csr

SET NOCOUNT off

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-12 : 15:01:55

Is Col001 the primary key?

Does this query produce the data that you want to see in the table?

SELECT t1.Col001, t1.Col002, t2.Col003
FROM Table1 t1
INNER JOIN Table1 t2
ON t1.Col001 = t2.Col001
WHERE t1.Col002 IS NOT NULL AND t2.Col003 IS NOT NULL

Tara

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-09-12 : 15:19:38

Hi Tara, Sorry for the confusion. Here are the columns that I am working with for the update: (There are more)

Col001 Last Name
Col002 First Name
Col003 Middle Name
Col006 SSN
Col007 Drivers License

I don't have a Primary Key on the table but I could add another column to the data set to uniquely identify each record. The problem is still the same. I have 1 million records that I need to correct. This is an example of the existing data:

Col001 Col002 Col003 Col006 Col007
Jones Mike J CA123456
Jones Mike J 123-45-6789

What I am trying to do is get 500,000 records that appear:

Col001 Col002 Col003 Col006 Col007
Jones Mike J CA123456 123-45-6789

Any help would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-12 : 15:24:24

How do you know which rows go with each other? Which columns show that?

Also, please post DDL for your table, sample data in the form of INSERT INTO statements, and the expected result set using that sample data so that we don't keep going back and forth on what it actually looks like. This way we can test it out on our own machines.

Tara

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-09-12 : 16:06:42

I am using the Drivers License Column (Col006) to get a distinct list. The problem is the SSN is on another record. The two records are duplicate with the exception of Col006 and Col007. Col006 will have the DL, Col007 (NULL) on one record and the next record will be (NULL) Col006 and Col007 will have the SSN.

I have updated the script that I am using with the proper column names, provided the DDL for the table, and two insert statements for the sample of the existing data along with what I would like to accomplish. Thanks for your help!

SET NOCOUNT on

DECLARE @getlname VARCHAR (50)
DECLARE @getfname VARCHAR (50)
DECLARE @getmname VARCHAR (50)
DECLARE @getdl VARCHAR (50)
DECLARE @getssn VARCHAR (50)

DECLARE dl_csr CURSOR FOR

SELECT distinct DriversLicense from Table1 where DriversLicense is not null

OPEN dl_csr
FETCH dl_csr INTO @getdl

WHILE @@fetch_status = 0
BEGIN
SET @getlname = (SELECT TOP 1 LName FROM Table1 where DriversLicense = @getdl)
SET @getfname = (SELECT TOP 1 FName FROM Table1 where DriversLicense = @getdl)
SET @getmname = (SELECT TOP 1 MName FROM Table1 where DriversLicense = @getdl)
SET @getssn = (SELECT TOP 1 SSN from Table1 where SSN is not null and LName = @getlname and FName = @getfname and MName = @getmname)
update Table1 set SSN = @getssn where SSN is null and LName = @getlname and FName = @getfname and MName = @getmname and DriversLicense = @getdl

FETCH dl_csr INTO @getdl
END

CLOSE dl_csr
DEALLOCATE dl_csr

SET NOCOUNT off

CREATE TABLE [dbo].[Table1] (
[LName] [varchar] (100) NULL ,
[FName] [varchar] (100) NULL ,
[MName] [varchar] (100) NULL ,
[DL_SSN_Id] [varchar] (100) NULL ,
[Misc] [varchar] (100) NULL ,
[DriversLicense] [varchar] (100) NULL ,
[SSN] [varchar] (100) NULL ,
[AreaCode] [varchar] (100) NULL ,
[Phone_Num] [varchar] (100) NULL ,
[Type] [varchar] (100) NULL ,
[BAddress] [varchar] (100) NULL ,
[BAddress1] [varchar] (100) NULL ,
[BCity] [varchar] (100) NULL ,
[BState] [varchar] (100) NULL ,
[BZipcode] [varchar] (100) NULL ,
[SAddress] [varchar] (100) NULL ,
[SCity] [varchar] (100) NULL ,
[SState] [varchar] (100) NULL ,
[SZipcode] [varchar] (100) NULL
) ON [PRIMARY]
GO

INSERT TABLE1 VALUES ('BUIEU','SUSANNA','P','DL',NULL,'CAB0514764',NULL,'550','285-7039','RESIDENTIAL','516 HIGH RD',NULL,'SAN DIEGO','CA','92304','512 HIGH RD','SAN DIEGO','CA','92304-1885')
INSERT TABLE1 VALUES ('BUIEU','SUSANNA','P','DL',NULL,NULL,'123-45-6789','550','285-7039','RESIDENTIAL','516 HIGH RD',NULL,'SAN DIEGO','CA','92304','512 HIGH RD','SAN DIEGO','CA','92304-1885')

Expected result:

INSERT TABLE1 VALUES ('BUIEU','SUSANNA','P','DL',NULL,'CAB0514764','123-45-6789','550','285-7039','RESIDENTIAL','516 HIGH RD',NULL,'SAN DIEGO','CA','92304','512 HIGH RD','SAN DIEGO','CA','92304-1885')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-12 : 16:32:40

I joined on LName, FName, and Phone_Num.

Here you go:



SELECT 
	t1.LName, t1.FName, t1.MName, t1.DL_SSN_Id, t1.Misc, t1.DriversLicense, 
	t2.SSN, t1.AreaCode, t1.Phone_Num, t1.Type, t1.BAddress, t1.BAddress1, 
	t1.BCity, t1.BState, t1.BZipcode, t1.SAddress, t1.SCity, t1.SState, t1.SZipcode
FROM Table1 t1
INNER JOIN Table1 t2
ON t1.LName = t2.LName AND t1.FName = t2.FName AND t1.Phone_Num = t2.Phone_Num
WHERE t1.SSN IS NULL AND t2.DriversLicense IS NULL

Tara

cgunner
Yak Posting Veteran

95 Posts

Posted - 2005-09-13 : 09:31:03

That is PERFECT. Thanks you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources