deleting duplicates and updating relationships

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 duplicates and updating relationships

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-12 : 14:01:34

i'm transferring data from an old, poorly designed database to our new database. the following query gives me a list of all my duplicate customers and their corresponding transactions. one to one relationship. how can i delete all but one customer record and update all transactions for that customer to relate to the customer record i leave?

select * from tblcustomers c
left join tblTransactions t on t.intcustomerid=c.intcustomerid
where vcssn in(
select vcssn from tblcustomers group by vcssn having count(*)>1)
order by vcssn,c.intcustomerid

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-12 : 15:20:47

Which duplicate do you want to keep?

Is the data identical on the dups?

If it is, all you need is SELECT DISTINCT....

If it's not, then you have to make a decision.....

Brett

8-)

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-07-13 : 11:32:35

i want to select the duplicate to keep by using the most recent datetime field in the record. field name is dtCreated

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-13 : 14:09:29

Did you try something like:


CREATE TABLE myTable99(Col1 int, dtCreated datetime)
GO

INSERT INTO myTable99(Col1, dtCreated)
SELECT 1, '1/1/2001' UNION ALL
SELECT 1, '1/1/2002' UNION ALL
SELECT 1, '1/1/2003' UNION ALL
SELECT 2, '1/1/2001' UNION ALL
SELECT 2, '1/1/2004'
GO


     SELECT * INTO myTable00
       FROM myTable99 a
      WHERE EXISTS (
		  SELECT Col1 
		    FROM myTable99 b
		   WHERE a.Col1 = b.Col1 
		GROUP BY b.Col1
		  HAVING a.dtCreated = MAX(b.dtCreated) 
		   )		
GO

SELECT * FROM myTable00
GO

DROP TABLE myTable99
DROP TABLE myTable00
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources