deleting duplicate records - 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

SQL Server Development (2000)

deleting duplicate records

Author

Topic

yaditzal
Starting Member

22 Posts

Posted - 2010-04-07 : 09:40:41

hello.
I need help with this:

I have a table called aux_addr
sample:
LOCATION_ID LOC_ADDR1 LOC_CITY LOC_STATE LOC_ZIP CLIENT_ID
1 8201 W BROWARD BLVD MIAMI FL 33152 89856 2 8201 W BROWARD BLVD MIAMI FL 33152 M5623
3 8201 W BROWARD BLVD MIAMI FL 33152 P856

I NEED TO SEPARATE THIS IN TO TABLES. SOMETIHNG LIKE THIS.

TABLE ADDRESS (WHERE I GOING TO HAVE ONLY ONE ENTRY FOR THE SAME LOCATION)
SAMPLE

LOCATION_ID LOC_ADDR1 LOC_CITY LOC_STATE LOC_ZIP
1 8201 W BROWARD BLVD MIAMI FL 33152

AND THEN ANOTHER TABLE CALL CLIENT_LOCATIONS WHERE I GOING TO HAVE DIFERENTS CLIENTS WITH THE SAME LOCATION_ID LIKE THIS:

CLIENT_ID LOCATION_ID
89856 1
M5623 1
P856 1

NOTE:

THE LOCATION_ID HAS TO BE SOMETING GENERATED AUTOMATIC 1 TO N.
WHEN I GOING TO SELECT JUST ONE OF THE DUPLICATED ADDRS I HAVE TO COMPARE THE NUMBERS IN THE LOC_ADDR1 COLUNM AND THEN THE ZIP CODE TO MAKE SURE IT IS THE SAME ADDRS BECAUSE IN SOMETIMES I GOING TO HAVE THE SAME ADDRS WRITE DIFERENTS LIKE
8201 W BROWARD BLVD AND
8201 WEST BROWARD BL

I DONT KNOW HOW TO DO THAT , I THINK I NEED AN STORE PROCEDURE OR A FUNCTION BECAUSE I HAVE TO GO RECORD BY RECORD AND THEN INSERTING IN THE ANOTHER TWO TABLES.

PLEASE ANY HELP WITH THIS. THANKS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 13:53:13

the LOCATION_ID is already in auxrite_addr? then why generate it again?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaditzal
Starting Member

22 Posts

Posted - 2010-04-07 : 13:58:38

well I dont know, I just I was thinking because when you delete the similar rows you are going to need a location id for the row you select not to delete

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 14:08:19

wont this be enough?


INSERT INTO [TABLE ADDRESS]
SELECT DISTINCT LOCATION_ID LOC_ADDR1 LOC_CITY LOC_STATE LOC_ZIP
FROM aux_addr 

INSERT INTO CLIENT_LOCATIONS
SELECT DISTINCT LOCATION_ID,CLIENT_ID
FROM aux_addr

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaditzal
Starting Member

22 Posts

Posted - 2010-04-07 : 14:47:21

UH IIT IS NOT WORKING, WHEN I DO SELECT DISTINCT LOCATION_ID LOC_ADDR1 LOC_CITY LOC_STATE LOC_ZIP
FROM aux_addr
I DONT GET THE SAME WHEN I DO
SELECT DISTINCT LOCATION_ID,CLIENT_ID
FROM aux_addr
REMEMBER THAT THE LOCATION_ID IS UNIQUE
ANOTHER THINK I NEED TO KNOW WAHT IS THE ADDR BY CLIENT.
PLEASE HELP WITH THIS
THANKS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 01:14:32

quote:
Originally posted by yaditzal

UH IIT IS NOT WORKING, WHEN I DO SELECT DISTINCT LOCATION_ID LOC_ADDR1 LOC_CITY LOC_STATE LOC_ZIP
FROM aux_addr
I DONT GET THE SAME WHEN I DO
SELECT DISTINCT LOCATION_ID,CLIENT_ID
FROM aux_addr
REMEMBER THAT THE LOCATION_ID IS UNIQUE
ANOTHER THINK I NEED TO KNOW WAHT IS THE ADDR BY CLIENT.
PLEASE HELP WITH THIS
THANKS

Sorry I didnt get you. As per your sample what i felt was you're looking at a way to populate TABLE ADDRESS and CLIENT_LOCATION from your data. Can you elaborate with data sample what you mean by you're not getting same o/p
Also please refrain from using caps while posting.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yaditzal
Starting Member

22 Posts

Posted - 2010-04-08 : 08:58:47

I found the solution, I did more or less what you told me.
I select distinct addr and inserted into addr table then i when updating from addr_aux the rest of the info and inserting the client_id
thanks so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 11:06:51

ok cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources