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.
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
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
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.
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 ?
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..
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).
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).
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.
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)
/****** 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:'.
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
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.
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?
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.
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
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 **********************************************/
--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
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?
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?
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!
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?
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!
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
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..................
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?
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?
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.
