Data dublicated - 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

Other Forums

SQL Server 6.5 \ SQL Server 7.0

Data dublicated

Author

Topic

medotnet
Starting Member

14 Posts

Posted - 2007-08-18 : 04:27:18

Hi,

I recently had a problem that my code duplicated some records in my database (well quite many), I fixed the coding part but still the database part needs your assistance.
I now have like 5 similar records and want to remove 4 of them to get correct data.

I have made a query to do so, but it removed all the records, I guess I need some sort of stored procedure or function that contains a counter..

well any ideas please I need them I'm running out of time..

thanks in advance,

Adi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-18 : 04:38:32

Are you using SQL Server 2000 or SQL Server 2005?
There are many examples in the FAQ posted at the "New to SQL Server" forum.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-18 : 04:44:48

http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

medotnet
Starting Member

14 Posts

Posted - 2007-08-20 : 04:00:59

Well thanks a lot,
I went through them but my case is a little complicated,
please assume the following:
drop table purchase
go
create table purchase (VNo char(10),AccDB int,AccCR int,AmountDB float,amountCR float)
/*my table comes without unique id so you can add identity column if necessary.*/
insert purchase select 'SV-1',1,2,0,600
insert purchase select 'sv-1',2,1,600,0
insert purchase select 'sv-2',1,2,0,500
insert purchase select 'sv-2',1,2,0,500
insert purchase select 'sv-2',1,2,500,0
insert purchase select 'sv-2',2,1,500,0
insert purchase select 'sv-2',2,1,500,0
insert purchase select 'sv-2',2,1,0,500

the desired result would be something like this:
'SV-1',1,2,0,600
'sv-1',2,1,600,0
'sv-2',1,2,0,500
'sv-2',2,1,500,0
that is to keep 1 of the two duplicate rows and delete the third one because it is void, to make this easier a simple calculation can be done to identify which row to delete:
take the three dups of 'sv-2'
'sv-2',1,2,0,500
'sv-2',1,2,0,500
'sv-2',1,2,500,0

sum the amountcr and amountdr group by accDB
the result will be: 500,1000
deduct the results and place the positive value in it place as:
delete the 3 rows,
insert a new row with the correct values
insert purchase select 'sv-2',1,2,0,500
and so on..
I hope I succeeded with delivering my problem to you, and appreciate any help..

Thanks,
Adi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 04:26:53

You still haven't asnwered my question, wether or not you are using SQL Server 2005.

E 12°55'05.25"
N 56°04'39.16"

medotnet
Starting Member

14 Posts

Posted - 2007-08-20 : 04:42:55

I'm using SQL server 7.0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-20 : 13:50:07

I moved this thread to the 7.0 forum since this is not a data corruption issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources