January 15, 2007 at 1:58 pm
This is my table;
id lastcontact
a 5/2/2004
b 6/3/2001
a 5/2/2004
c 4/2/2006
d 5/3/2000
and so on....
I need to delete all the duplicate rows that have the same id and lastcontact values,...so at the end of my query, my table would look like this;
a 5/2/2004
b 6/3/2001
c 4/2/2006
d 5/3/2000
....please tell me the syntax I need to use....thank you so much
January 15, 2007 at 2:02 pm
Its pretty hard to help you if you don't tell us what you are trying to do. If you need help with the DELETE syntax, either find DELETE in BOL, or post a description of what you want to achive with your DELETE. Do you want to remove all rows from your table? Only rows that meet a certain criteria? Please be more descriptive and include table DDL and example data. It's easier to get help that way.
January 15, 2007 at 2:06 pm
sorry John...I hit too many buttons at the same time and all of a sudden my post was up on the bulletin board before I was able to finish...are you able to understand now that I need help with the delete syntax and what Im trying to accomplish ?
January 15, 2007 at 2:09 pm
I know that you probably want the answer straight away, but if you did a search with Google on deleting duplicate records in a database, you will come up with a large number of approaches to do this.
January 15, 2007 at 2:18 pm
James is correct, there are many different approaches to doing this. How large is your table? How many dupilcates to exect to DELETE? Is there a Primary Key or unique index on this table?
January 15, 2007 at 2:37 pm
Yes, you're both right...I could google it, but since I joined this forum, I thought I could cut through all the fluff and just get a straight and solid answer right away and see how you would tackle this problem.
My table has 240,000 rows...and there is a index on the id field....I would appreciate a delete syntax that either one of you would use in this situation..otherwise I'll spend some time on google.
I currently have 700 id's that occur in this table more then once by running this query;
SELECT id, COUNT(id) AS occurence, LASTCONT
FROM CONTACT2
GROUP BY id, LASTCONTON
HAVING (COUNT(id) > 1)
If you could help, I'd really appreciate it.
January 15, 2007 at 2:41 pm
What are the columns that uniquelly identify the row (need some sort of tie breaker for this code)?
Can you add an identity column (teporary measure) to that table?
January 15, 2007 at 3:11 pm
With that few number of duplicates, I would recommend the following apprach (in pseudo code):
1. Create @table or #table temporary storage table to mimic the Contact2 table schema.
2. INSERT into above table duplicate rows.
3. DELETE duplicate rows from Contact2 table.
4. INSERT rows from temporary table into Contact2, filtering out duplicates.
If your ID column is an IDENTITY, take that into account when moving the data. You may need to list out the coulmn names when INSERTing back into Contact2 and let SQL Server assign new IDs depending on your requirements. If you can give us your table DDL and the tie-breaker logic that Remi is asking for, you may get a more detailed solution.
January 15, 2007 at 5:13 pm
The column that uniquely identifies a row is the id column...and it has a primary index attached to it.
Somehow that has become corrupt, and now find myself with multiple duplicate id's in the table.
I dont have to be biased which duplicate row I delete, I just need to delete one row of them (if I have it occuring twice) or two rows if if have the id occuring 3 times.
John..what you point out in step 3 above, is excactly what Im trying to figure out how to do.
Again, I dont know if Im being clear, but I dont care which of the duplicate rows I delete, I just need there to be one unique id and its corresponding data
below is the script of my real contact2 table..... id = accountno, and lastcont = lastconton
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTACT2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CONTACT2]
GO
CREATE TABLE [dbo].[CONTACT2] (
[ACCOUNTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CALLBACKON] [datetime] NULL ,
[CALLBACKAT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CALLBKFREQ] [smallint] NULL ,
[UREV] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTCONTON] [datetime] NULL ,
[LASTCONTAT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTATMPON] [datetime] NULL ,
[LASTATMPAT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UATELRNK] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKEYCC] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOTHERKCC] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTTLREPBD] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USPONSSTAT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCONFDATE2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCONFSITE2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UESTATT2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOTHERPP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEETDATEON] [datetime] NULL ,
[MEETTIMEAT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMMENTS] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PREVRESULT] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NEXTACTION] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTIONON] [datetime] NULL ,
[CLOSEDATE] [datetime] NULL ,
[varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[U12P] [float] NULL ,
[U12PDD] [float] NULL ,
[U1999] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UATELGCF] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[U1999DATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[U2000] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[U2000DATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[U3MOST] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UADDLCMT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UALTADDR] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UALTCITY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UALTSTATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UALTSUITE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UALTZIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UANN] [datetime] NULL ,
[UATEL2FND] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UATELWEB] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UBDRANKING] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UBIGCHALLE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UBILLRATE] [float] NULL ,
[UCHILD1] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCHILD2] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCHILD3] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCHILD4] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCMKDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOLDSELLST] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCONF] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCONFCODE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCONTDET] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCSEM] [float] NULL ,
[UCURMKT] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDATEBIGCH] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDATEDPPS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDBA] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDDATTN] [float] NULL ,
[UDDNOTE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDDNOTEDT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDEADLEAD] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDEPT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDOLDPPS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDPPSOLD] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UAGCF3] [float] NULL ,
[UFUND10TWO] [float] NULL ,
[UAVI2] [float] NULL ,
[UDRIVE] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UEVERCOMBN] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UEVERUSED] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UF10] [float] NULL ,
[UFBD8CY] [float] NULL ,
[UFBD9CY] [float] NULL ,
[UFBDTOT1] [float] NULL ,
[UFBDTOT2] [float] NULL ,
[UFBDTOT3] [float] NULL ,
[UFBDTOT4] [float] NULL ,
[UFBDTOT5] [float] NULL ,
[UFBDTOT6] [float] NULL ,
[UFBDTOT7] [float] NULL ,
[UFBDTOT8] [float] NULL ,
[UFBDTOT9] [float] NULL ,
[UFDATE1] [datetime] NULL ,
[UFDATE10] [datetime] NULL ,
[UFDATE102] [datetime] NULL ,
[UFDATE2] [datetime] NULL ,
[UFDATE3] [datetime] NULL ,
[UFDATE4] [datetime] NULL ,
[UFDATE5] [datetime] NULL ,
[UFDATE6] [datetime] NULL ,
[UFDATE7] [datetime] NULL ,
[UFDATE8] [datetime] NULL ,
[UFDATE9] [datetime] NULL ,
[UFFUNDSEL] [datetime] NULL ,
[UFPLPDATE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UFPLPSALES] [float] NULL ,
[UFUND10CY] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UFUND1CY] [float] NULL ,
[UFUND1TOT] [float] NULL ,
[UFUND2CY] [float] NULL ,
[UFUND2TOT] [float] NULL ,
[UFUND3CY] [float] NULL ,
[UFUND3TOT] [float] NULL ,
[UFUND4CY] [float] NULL ,
[UFUND4TOT] [float] NULL ,
[UFUND5CY] [float] NULL ,
[UFUND5TOT] [float] NULL ,
[UFUND6CY] [float] NULL ,
[UFUND1210T] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UFUND6TOT] [float] NULL ,
[UFUND7CY] [float] NULL ,
[UFUND7TOT] [float] NULL ,
[UFUND8CY] [float] NULL ,
[UFUND8TOT] [float] NULL ,
[UFUND9CY] [float] NULL ,
[UFUND9TOT] [float] NULL ,
[UFUNDXI] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UFUNDX] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UGEO] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHADDR1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHADDR2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHOBBIES] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHOBDATE] [datetime] NULL ,
[UHOMECITY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHOMESTATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHOMEZIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHOWATEL] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHOWHELP] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UINTHEBIZ] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKARKALLOW] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKEYNOTE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULICDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULICHELD] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULONGBUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULPSALES] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULUNDIN] [float] NULL ,
[UMARKEXP] [float] NULL ,
[UMKTALLOW] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UMKTNOTE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UMKTNOTEDT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UMOSTDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UMOSTF] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNATDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNATIONAL] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNEWSELLER] [datetime] NULL ,
[UNOREGREPS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNOTES] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOMKT] [float] NULL ,
[UONADDR] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UONCITY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UONSTATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UONSUITE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UONZIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOTHERDPPS] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPASSWORD] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPBDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPCNTDPPS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPERDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPERGOAL] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPERSP] [float] NULL ,
[UPERWEB] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPGDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPHONEH] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPHONEHMFX] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPLEDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPRIBUSINE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPROFGOAL] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPSPODATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPSPONSOR] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UR12P] [float] NULL ,
[UR12PDD] [float] NULL ,
[URCONF] [float] NULL ,
[UREFDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREFERDBY] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREFERTEST] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREG] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREGDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREMPOCKET] [float] NULL ,
[UREP722LIC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKEYDD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UKEYMKTG] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTTLCALLQ1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTTLCALLQ2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTTLCALLQ4] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTTLCALLQ3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTTLREPS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTTLCNTS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULASTREPLI] [datetime] NULL ,
[UFNDXSLR] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UFNDIXSELR] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UHOT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UWARM] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCOLD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNEWPROSP] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDEAD] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UMKTOPTY] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UOTHRPSALE] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCONFDATES] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UCONFSITE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UESTATTEN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UMKTGCOSTS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UMKTCOSTS] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UDUEDLGFEE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREPBD] [datetime] NULL ,
[UREPCOMM] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREPDEMOGR] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREPDPPPCT] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UREPVNTDT] [datetime] NULL ,
[UREPVNTFT] [float] NULL ,
[UREPVNTRE] [datetime] NULL ,
[UREPVNTYT] [float] NULL ,
[UROMKT] [float] NULL ,
[URPERSP] [float] NULL ,
[URREMPOCK] [float] NULL ,
[URSEMATT] [float] NULL ,
[URSEMCOST] [float] NULL ,
[URSPBEBE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URTSTD] [float] NULL ,
[USALESG1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USALESG2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USALG1DATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USALG2DATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USELLSTAT] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USEMATT] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USEMCOST] [float] NULL ,
[USERDEF01] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF02] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF03] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF04] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF05] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF06] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF07] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF08] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF09] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF10] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF11] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF12] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF13] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF14] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF15] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERDEF16] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USOURCE] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USPDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USPOUSE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USRCDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USTPART] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USUBMITALS] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTOP200] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTOPPROD] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTOYSENT] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTPDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UTSTD] [float] NULL ,
[UVEN1PS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UVEN1SP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UVENTDATE] [datetime] NULL ,
[UVENTFNDCY] [float] NULL ,
[UVENTFNDTO] [float] NULL ,
[UVENTURE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UWDLDATE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UWEBSITES] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UWHATATEL] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UWHICATEL] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UFundXI2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
January 15, 2007 at 11:14 pm
well either u can create a cursor and delete all the duplicate records. Somthing like this
Declare @id int,
@name varchar (50),
@cnt int,
@salary numeric
Declare getallrecords cursor local static For
Select count (1), id, name, salary
from employee (nolock)
group by id, name,salary having count(1)>1
Open getallrecords
Fetch next from getallrecords into @cnt,@id,@name,@salary
--Cursor to check with all other records
While @@fetch_status=0
Begin
Set @cnt= @cnt-1
Set rowcount @cnt
-- Deleting the duplicate records. Observe that all fields are mentioned at the where condition
Delete from employee where id=@id and name=@name and salary=@salary
Set rowcount 0
Fetch next from getallrecords into @cnt,@id,@name,@salary
End
Close getallrecords
Deallocate getallrecords
Or you can do one more thing
1) select * into temp_table from base_table where 1=0
2) create unique index temp_idx on temp_table(column name) with ignore_dup_key
3) insert temp_table select * from base_table.
January 16, 2007 at 10:54 am
Koln,
You say that AccountNo is your Primary Key? Do you have a Primary Key constraint created on that column or are you attempting to enforce uniqueness through your application/front-end? Also, this is a pretty wide table. Are all of the column values the same for each duplicate AccountNo?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply