Deleting Duplicate Rows

Feb 27, 2014

In my database, I have a table "tbl_c_extract" that consists of 4 columns that look the following. I'm looking at a daily batch of around 4000 records, of which 150 are likely to be duplicates.

Emp_No varchar(255), Proprietary_ID varchar(255), LeaveDateActual datetime
123456, E123456, 2014-09-27 00:00:00.000
213832, E123456, 2099-12-31 00:00:00.000
213836, E123456, 2014-01-31 00:00:00.000

In the example above, I need to remove 2 of the entries, leaving only the one that with the maximum leave date. In this case, those without a leave date have the 2099 entry.

Using CTE works exactly as I want it to, however SQL Server Agent doesn't seem to like the use of CTE..

Code:
WITH CTE (Proprietary_ID, LeaveDate, RN)
AS
(
SELECT Proprietary_ID, LeaveDate,
ROW_NUMBER() OVER(PARTITION BY Proprietary_ID ORDER BY Proprietary_ID, LeaveDate) AS RN
FROM tbl_c_extract
)
DELETE
FROM CTE
WHERE RN > 1

View 2 Replies


ADVERTISEMENT

Deleting Duplicate Rows

May 6, 2008

I have a csv file that I need to import daily into a SQL Server 2005 table. Much of the table contents could just be overwritten with the new csv file, however there are a set of Rows within the table that need to be appended to , rather than overwritten. 
There is no Primary Key in the csv file that can be used.  
I'm not sure this is the best approach, but what I have been trying to do, is append the entire csv file to the existing table, and then go back and delete the duplicates.
When I run the Delete, it does delete the majority of the records, but leaves a couple hundred behind. The number left behind varies with each run, can't seem to identify a pattern here. Running the Delete a second time does clean up the rows left behind in the first execution of the Delete, and gives the result I want.
Any thoughts as to why this needs to be run twice? Or is a better approach available?
Here is my code -
SELECT [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time], dupcount=count(*)
INTO temppkgactions
FROM pkgactions
GROUP BY [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]HAVING count(*) > 1
 
DELETE TOP (SELECT COUNT(*) -1 FROM dbo.temppkgactions WHERE dupcount > 1 )
FROM dbo.pkgactions
DROP TABLE temppkgactions
 
Thanks
 
 

View 2 Replies View Related

Deleting Duplicate Rows

Feb 11, 1999

Hai

I have problem in deleting duplicate rows. I have a identity column in my table, if I try to use correlatted sub query with Delete command it gives error.

The other problem I have is I have a date column in my table and update that column with current date and time. If use a query to fetch a records on a particular day , it does not return any rows

select * from rates where ch_date >='02/11/99' and ch_date<='02/11/99'

If I use convert also there is some other problems. Is there any way to force date checkings to be done excluding time.



Thanks

View 6 Replies View Related

Deleting Some Of Duplicate Rows

Nov 17, 1998

This is an imaginary problem while discussing ROWID in ORACLE.

Consider a table without primary key, unique key, uniuqe index.
A row has inserted into the table many times.
I want to delete all but one dulicated rows. With any 'where' clause all rows(duplicated)
will be deleted. In ORACLE i can achieve this using ROWID as follows:

Delete from Table_name
where < all column values >
and ROWID <> ( Select max(rowid) from Table_name where < all column values > )

How can this be achieved in MS SQL Server 6.5 ?

According to Dr. Codd's Golden rules for RDBMS one is that
One should be able to reach each data value in the database by using
table name, row idenfication value and column name.

Does MS SQL Server 6.5 satisfy this requirement ?

Also How many of Dr. Codd's 13 Golden Rules for RDBMS does MS SQL Server 6.5
Satisfy? Which doesn't ?

Any discussion about Codd's Rules is welcome.


- Gunvant Patil
gunvantp@yahoo.com

View 1 Replies View Related

Urgent - Deleting Duplicate Rows

Apr 5, 2001

Hi folks,

I need to delete the duplicate rows from a table. How to do that in SQL server 7.0 ? If possible write an example, so that it will be much useful for me..

Thanks for ur help..

rgds,
vJ

View 1 Replies View Related

Deleting Duplicate Rows In SQL Table

Nov 16, 2007

I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse

Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External


I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).

Any help would be greatly appreciated.
Thanks,

View 3 Replies View Related

Deleting Duplicate Rows In SQL Table

Nov 16, 2007

I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse

Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:


I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).

Any help would be greatly appreciated.
Thanks,

View 10 Replies View Related

Deleting Duplicate Rows Within A Single Table

May 14, 2004

I was wondering if anyone had a suggestion as to how to delete duplicate rows from a table. I have been doing this:

SELECT * INTO TempUsersNoRepeats
FROM TempUsers2
UNION
SELECT * FROM TempUsers3

This way I end up with a total of four tables (the fourth table being the original Users table) and I was hoping that there was a way that I could do this all within the the original Users table and not have to create the three TempUsers tables.

Thanks,
Ron

View 5 Replies View Related

Scheduling An Upload And Deleting Duplicate Rows

Nov 5, 2006

Hi,
New to this Database and this forum as I am I would like to ask for a couple of prompts. My SQL2000 tables are ready and I need to schedule Daily upload of .txt files. These contain a rolling 7Days of Stats.
Q1: How best to schedule the automiatic uploading of this data to the respective Tables in SQLServer.(Field names are identical), and
Q2: How to schedule a Daily Deletion of those rows which are in the tables already (Each day 6 Days must be Deleted and 1 kept)

Appreciate your help,

Gezza

View 6 Replies View Related

For Deleting Duplicate Rows Subquery Or Cueser Is Better In Performance????????????

Sep 26, 2006

For deleting duplicate rows, i can use cursor and subquery.

cursor code

Declare dup_cursor cursor for
select acctnumber from LinkUrnABSADMBAR
group by acctnumber
having count(*) > 1



Open dup_cursor

Fetch Next from dup_cursor INTO @acctnumber

While (@@Fetch_Status = 0)
Begin

Delete from LinkUrnABSADMBAR
where acctnumber = @acctnumber

Fetch Next from dup_cursor INTO @acctnumber
End

Close dup_cursor
Deallocate dup_cursor

Subquery code

delete from galupload2000..test where id in (select id from galupload2000..test group by id having count(*) >1)

My question is which one is Better in performance????????????



Thanks

Sandipan

View 2 Replies View Related

T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?

Jul 18, 2014

/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]

-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.

[Code] ...

*** Getting this error below when executing the code ***

Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.

View 9 Replies View Related

For Deleting Duplicate Row???????

Oct 5, 2006

Hi,

I want to delete duplicate row from a very big table. Actually this table is used by a SP, and it's a very important. Due to duplicate record entry it's falling. I use bellow method for discarding the dulicate record.

PLz tel me it's the most efficent way to this job or u have some other way

1. I drop the primary key

2. Then I let all the duplicate record came into the table

3. then I removed them by using Group by clause and setting rowcount(1 - group by count).

4. Put primary key back and update the statistics.

Code is

 If Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
 DROP INDEX AdjustmentTransactions.PrimaryKey

Insert into AdjustmentTransactions
(UrnABS, UrnBar, .................Description)
Select a.UrnAbs,
       a.UrnBar,
       a.TxnUrn.....................
       a.Description
from TxnProcess a
where a.InsUpFlag = 'I'
  and a.Processed = 'N'
  and ASCII(b.TxnType) = 65
 
Set @row_count=0
Declare dup_cursor cursor for
 Select UrnBAR,TxnUrnBarPat,count(*) counts from AdjustmentTransactions
 group by UrnBAR,TxnUrnBarPat having count(*) > 1
Open dup_cursor
Fetch next from dup_cursor into @VUrnBAR,@VTxnUrnBarPat,@count

While (@@Fetch_Status = 0)
Begin
 Select @row_count=@count-1
 Set rowcount @row_count
 Delete from AdjustmentTransactions where UrnBAR=@VUrnBAR and TxnUrnBarPat=@VTxnUrnBarPat
Fetch next from dup_cursor into @VUrnBAR,@VTxnUrnBarPat,@count
End
Set rowcount 0
Close dup_cursor
Deallocate dup_cursor
If not Exists (select * from SYSINDEXES where name='PrimaryKey' and id=Object_id('AdjustmentTransactions'))
   CREATE  UNIQUE  INDEX [PrimaryKey] ON [dbo].[AdjustmentTransactions]([UrnBAR], [TxnUrnBarPat]) ON [PRIMARY]
Update Statistics AdjustmentTransactions

 

Thanks

Sandipan

View 2 Replies View Related

Deleting Duplicate Records.

Jul 8, 1999

I need a sql statement to delete duplicate records.

I have a college table with all colleges in the nation.
I noticed that all of the colleges were listed twice.
How do I delete all of the duplicate records.

Here is my table.
Colleges
-------------------
schoolID - smallint NOT NULL,
schoolName - varchar(60) NULL

Can someone help me out with the sql statement???
I'm running SQL Server 6.5.

- ted

View 3 Replies View Related

Deleting Duplicate Records

Aug 27, 2004

Hi All,
I am having one table named MyTable and this table contains only one column MyCol. Now i m having 10 records in it and all the records are duplicate ie value is 7 for all 10 records.

It is something like this,

MyCol
7
7
7
7
7
7
7
7
7
7

Now i m trying to delete 10th record or any record then it gives me error
"Key column information is insufficient or incorrect. Too many rows were affected by update."

What should i do if i want only 4 records insted 10 records in my table?
How do i delete the 6 records from table?

Plz help me.


Regards,
Shailesh

View 3 Replies View Related

Deleting Duplicate Record

Nov 19, 2004

hi to all,

How to delete duplicate record in the recordset?

Thanks...

View 3 Replies View Related

Deleting Duplicate Records

Dec 1, 2006

gaurav writes "respected sir
here i have a question
how we can delete duplicate records through query in SQL Server
thanks"

View 2 Replies View Related

Deleting 'consecutive' Duplicate Records Alone

Aug 9, 2006

Rajarajan writes "Kindly don't ignore this as regular case.
This is peculiar.
I need to delete one of duplicate records only if they occurs consecutively.
eg.

1. 232
2. 232
3. 345
4. 567
5. 232

Here only the first record has to be delete. Kindly help me out.

Thank you.

Regards,
R.Rajarajan"

View 1 Replies View Related

Deleting The Duplicate Record From Table

Mar 26, 2008

Hi ,
i am using sql server 2005.
i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them
my problem is table structure have idendtity column which is unique.
that is m table structure is something like

recid citycode hospcode doctorcode otherdesp
1 0001 hp001 d0001 ...
2 0002 hp002 d0002 ...
3 0001 hp001 d0001 ...
4 0002 hp002 d0002 ...

please suggest

thank you

View 2 Replies View Related

Problem Deleting Duplicate Data

Sep 27, 2006

I have a table that contains more than 10,000 rows of
duplicate data. The script below copies the data to a temp table then
deletes from the original table. My problem is that after it runs, I now
have 122 rows of triplicate data (but dups are gone). If I rerun the script, it doesn't see the
triplicate data and returns 0 rows. I've use three different versions of
delete dup row scripts with the same result. There are no triggers or
constraints on the table, not even a primary key. What am I missing?-------------------------------------------------------------------

/**********************************************
Delete Duplicate Data
**********************************************/

--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
[student_test_uniq] [bigint] NULL,
[test_uniq] [int] NULL,
[concept_id] [smallint] NULL,
[test_id] [varchar](12) NULL,
[questions_correct] [smallint] NULL,
[questions_count] [smallint] NULL,
[percentage_correct] [decimal](6, 3) NULL,
[concept_response_count] [smallint] NULL
)

--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'

--Delete dup from original table
DELETE FROM crt_concept_score
FROM crt_concept_score
INNER JOIN #tempduplicatedata
ON crt_concept_score.student_test_uniq = #tempduplicatedata.student_test_uniq
AND crt_concept_score.test_uniq = #tempduplicatedata.test_uniq
AND crt_concept_score.concept_id = #tempduplicatedata.concept_id
AND crt_concept_score.test_id = #tempduplicatedata.test_id
AND crt_concept_score.questions_correct = #tempduplicatedata.questions_correct
AND crt_concept_score.questions_count = #tempduplicatedata.questions_count
AND crt_concept_score.percentage_correct = #tempduplicatedata.percentage_correct
AND crt_concept_score.concept_response_count = #tempduplicatedata.concept_response_count

--Insert the delete data back
INSERT INTO crt_concept_score
SELECT * FROM #tempduplicatedata

--Check for dup data.
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1

--Check table
-- SELECT * FROM crt_concept_score

--Drop temp table
DROP TABLE #tempduplicatedata
GO

View 1 Replies View Related

Deleting Duplicate Records From A Table.....

Jul 6, 2006

I loaded one table via SSIS and found that it contained many duplicate records (from the input source). I can create a SQL task to delete them, but I wonder if SSIS offers and task "out of the box" to delete dups?



TAI,



barkingdog

View 6 Replies View Related

Deleting Duplicate Records From Lots Of Tables

Aug 29, 2006

Hi All,

So.. I'm a complete newb to SQL stuff.

I managed to find the 'Deleting Duplicate Records' from SQLTeam.com (thanks, by the way!!).. I managed to modify it for one of my tables (one of 14).


-- Add a new column

Alter table dbo.tblMyDocsSize add NewPK int NULL
go

-- populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update dbo.tblMyDocsSize
SET @intCounter = NewPK = @intCounter + 1

-- ID the records to delete and get one primary key value also
-- We'll delete all but this primary key
select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK)
into #dupes
from dbo.tblMyDocsSize
group by strComputer, strATUUser
having count(*) > 1
order by count(*) desc, strComputer, strATUUser

-- delete dupes except one Primary key for each dup record
deletedbo.tblMyDocsSize
fromdbo.tblMyDocsSize a join #dupes d
ond.strComputer = a.strComputer
andd.strATUUser = a.strATUUser
wherea.NewPK not in (select PKtoKeep from #dupes)

-- remove the NewPK column
ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK
go

drop table #dupes


Now that I've got that figured out, I need to write the same thing to fix the other 13 tables (with different column info)- and I'll need to run this daily.

Basically I've put together some vbscript that gathers inventory data and drops it into an MSDE db (sorry - goin for 'free' stuff right now). Problem is it has to run daily so that I'm sure to capture computers that turned on at different times etc which ever-increases my database 'till I bounce off the 2GB limit of MSDE.

So the question is, what would be the best way to do this? Can I put the code into a stored procedure that I can execute each day?


Thanks for your help....

View 4 Replies View Related

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View 4 Replies View Related

Deleting Rows

Nov 26, 2004

I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?

Thanks

View 8 Replies View Related

Need Help With Deleting Rows

Apr 3, 2007

Hi i'm a learner of sql,

i need help in deleting rows from a table that have multiple occurences of a value.

the table has a column named product_id , i need to find out what rows have the product_id value repeated ( i.e not distinct)

how do i do that help please.

View 1 Replies View Related

Deleting Rows

Apr 21, 2008



I have an issue that I don't know how to figure out.



I have a table that has a list of files in it. This table also has GUIDs associated with each file.



Some of these files were found in the emporary internet files folder on the systems scanned.



I want to remove all rows where the file path contains emporary internet files

The problem is that there are other tables where the file GUIDs are referrenced.

So when I try this:
DELETE FROM osScanFile
WHERE (FilePath LIKE '% emporary internet files\%')

I get an error saying that "The DELETE statement conflicted with the REFERENCE constraint "blah blah blah". ...

Basically there are rows in other tables that reference the rows I'm trying to delete in this table.

So how do I get it to delete not only the rows with the search criteria in it but also all rows from all other tables where the rows are linked?

Thanks,
Terry

View 3 Replies View Related

Deleting Rows From Many Tables

May 19, 2006

What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>"
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID"
SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)">
<DeleteParameters>
<asp:Parameter Name="UserID" Type="Int32" />
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID
DELETE FROM [photo] WHERE [UserID] = @UserID;
DELETE FROM [album] WHERE [UserID] = @UserID;
DELETE FROM [comment] WHERE [UserID] = @UserID;
...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!

View 8 Replies View Related

Deleting Rows Using A Join

Aug 27, 2001

I have two tables. Table 1 contains a distinct ID(3,4,5). Table 2 contains multiple ID's(3,3,3,3,3,4,4,4,5,5,5). I want to be able to use a join. If an ID is found in table 2, remove all entries of it. Any ideas? Thanks...

delete b.id
from table1 a join table2 b on a.id = b.id

View 1 Replies View Related

Deleting All Rows In A Table

Aug 6, 2004

hello there,
what is the code to Delete all rows in a Table????

thanks in advance

View 4 Replies View Related

Deleting Duplicated Rows

May 23, 2004

Hi,
I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies..................


thnx

View 14 Replies View Related

Need Help Deleting Rows In A Table

Feb 1, 2007

I need to delete some specific rows in Table1

int1 must have the value 1
int2 must have the value 1
int3 must have the value 0
and now we get to the difficult part - first character in the field [Cost No] have to be different from the letter 'B'. [Cost No] have the datatype varchar(20)

I expected the code to look something like this:
DELETE Table1
FROM Table 1
WHERE ([Int1] = 1) AND ([Int2] = 1) AND ([Int3] = 0) AND ((LEFT([Cost No]), 1) <> 'B')

But I get this error message:
The left function requires 2 arguments

What am I doing wrong and what should the right code look like?

View 3 Replies View Related

Deleting Table Rows And Optimization

Dec 10, 2001

i have 6 read only tables that every night all the data gets dumped and a new updated copy of the data is copied over. the tables range from 500,000 rows to almost 4 million. i have indexes set up on the fields i use to query against. my questions are
1.since i dump all the data every night and replace it, do i need to rebuild the indexes every night or is that done after the data is reentered?
2.i want to use a fill factor on the table since it is read only, but will dumping the data every night and reinputting it have adverse affects with a fill factor?
3.should i be shrinking the database or defragging it everynight cause of my data dumps and reloads?

thanks in advance

spiral

View 1 Replies View Related

Deleting Thousands Of Rows In A Table

Apr 10, 2003

How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())

increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.

Anyone has an idea?

mipo

View 3 Replies View Related

Deleting 8 Million Rows From Database

Jul 16, 2013

i am deleating 8 Million rows from my database,I am wondering how to control T-Log,also I heard something about row lock and table lock

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved