Delete Duplicate Records Without Primary Key
Feb 5, 2012
I want to delete the duplicate record from a table keeping 1 record aside.
My base table is-info
idnameclass
2abc6a
3abc6a
4abc6a
1abc6a
2abc6a
4abc6a
4abc6a
3abc6a
3abc6a
1abc6a
2abc6a
5abc6a
id-int
name-text
class-varchar
(there is no primary key in this table)
Now I want the result in following way:
idnameclass
2abc6a
3abc6a
4abc6a
1abc6a
I have tried the following query and its running fine but its not a dynamic stuff.
DELETE top (SELECT COUNT(*)-1 FROM aaa WHERE id ='3') --or put some number
FROM aaa
WHERE id ='3'-- or put some number
View 14 Replies
ADVERTISEMENT
Jul 30, 2004
I use a tabel for storin log data from a mail server. I noticed that I'm getting duplicate records, is there a way to delete the socond and/or third entry so I dont have any duplicates?
I need this done in SP.
View 1 Replies
View Related
Oct 19, 2000
Sorry for the new thread.
I have a userprofile table. There are a lot of duplicate records in this table. e.g.
USERID-----LASTNAME---EMAILADDRESS----CREATEDATE
----------------------------------------------------------------------
1----------A-----------A@yahoo.com---------2000-09-05 16:07:00.000
2----------A-----------A@yahoo.com---------2000-09-10 16:07:00.000
3----------A-----------A@yahoo.com---------2000-09-15 16:07:00.000
Userid is auto number, lastname and emailaddress are PK.
I want to delete duplicate records. If lastname and emailaddress are the same, only keep a record which createdate is the most newest date. See above example I only want to the record which userid is 3. I have alreday created a code which I attached below. This code onle keep a record which userid is 1.
Anybody can help me to solve this problem? Thanks.
============== My current code ====================
delete from userprofile where userprofile.userid in
--list all rows that have duplicates
(select p.userid
from userprofile as p
where exists
(select lastname, emailaddress
from userprofile
where lastname = p.lastname and emailaddress = p.emailaddress
group by lastname, emailaddress
having count (userid)>1))
and userprofile.userid not in
--list on row from each set of duplicate
(select min(p.userid)
from userprofile as p
where exists
(select lastname, emailaddress
from userprofile
where lastname = p.lastname and emailaddress = p.emailaddress
group by lastname, emailaddress
having count (userid)>1)
group by lastname, emailaddress)
View 2 Replies
View Related
May 7, 2008
Hello everyone,
I have a little dilemma. I have a table ALLTABLE that has duplicate records and I want to delete them. ALLTABLE has these columns with these values for example:
Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 25476 63 31 2
ADC-WC-0010005-0 25476 63 31 3
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1
ADC-WC-0010344-0 515 63 01 2
As you can see there is some duplicates in the first 3 rows and the final 2 (the entity number is the only difference). I want the table to look like this:
Policy Premium Class State Entity Number
ADC-WC-0010005-0 25476 63 31 1
ADC-WC-0010005-0 1457 63 29 4
ADC-WC-0010092-1 2322 63 37 1
ADC-WC-0010344-0 515 63 01 1
Thank you so much for the help. It is really appreciated.
View 11 Replies
View Related
Jun 5, 2008
Hi Guys,
I have the following table
customerid customername
------------------------
1 AAA
1 AAA
2 BBB
2 BBB
2 BBB
3 CCC
3 CCC
Here, I need to delete duplicate records from the above table.
After deleting the duplicate records the table should be
like this:
customerid customername
------------------------
1 AAA
2 BBB
3 CCC
Can any one help me!!!!!!
Regards
js.reddy
View 3 Replies
View Related
Jun 24, 2008
Hi,
Please help me in constructing a query that will delete duplicate records in a table;
Please see my table structure below:
CREATE TABLE LATEST_DATA (
[ID] int NOT NULL,
[IPage] varchar(100) NULL,
[IDevice] varchar(255) NULL,
[IGroup] varchar(255) NULL,
[IField] varchar(255) NULL,
[IValue] varchar(255) NULL,
[IIcon] int NULL,
[IID] int NULL,
[ReportID] int NOT NULL,
[RVersion] varchar(255) NULL,
[RHost] varchar(255) NULL,
[RUser] varchar(255) NULL,
[RLocation] varchar(255) NULL,
[RDateTime] varchar(16) NULL,
[RComplete] bit NOT NULL
);
-- Insert Latest data
INSERT
INTO LATEST_DATA
SELECT
[Item].[ID],
[Item].[IPage],
[Item].[IDevice],
[Item].[IGroup],
[Item].[IField],
[Item].[IValue],
[Item].[IIcon],
[Item].[IID],
[Item].[ReportID] ,
[Report].[RVersion],
[Report].[RHost],
[Report].[RUser],
[Report].[RLocation],
[Report].[RDateTime],
[Report].[RComplete]
FROM
[dbo].[Item],
[dbo].[Report]
WHERE
[Report].[ID] = [ITEM].[ReportID] AND
[Report].[ID] = ( SELECT
MAX([Report].[ID])
FROM
[dbo].[Report]
);
thanks
View 8 Replies
View Related
Apr 9, 2008
I have inherited a database with no primary keys in it. :(
There are, of course, duplicate records by the "logical key", i.e., the list of columns that should have been used in a primary key, if they only had one. I can easily identify when I have duplicate records using group by and having clauses. That's the easy part.I can identify duplicate records by "key columns" and the subset of those records that are "identical duplicates on all columns". I need to delete the "extra copies"
I can't do it by hand using sql server manager, it won't let me, because it recognizes that more than one row matches that row. In Oracle, I would use the pseudo-column "rowid", which would expose the internal identifier for the row.SqlServer doesn't appear to have this concept. is there another way other than creating an empty shadow table, copying all the distinct duplicate records into the shadow table, deleting all the duplicates in the old table, then copying the distinct duplicates back into the old table.
View 3 Replies
View Related
Nov 29, 2004
Hello All,
Please show me how to delete duplicate records from a table.
Thanks in Advance
View 1 Replies
View Related
Jul 20, 2005
I uploaded some data about 2 or 3 times and it keep appending it to thetable.Now I want to keep only first duplicate and delete rest of.Suppose part number 123 has been added 3 times so I want to keep only 1record.Thanks
View 1 Replies
View Related
Jul 7, 2007
I was importing records via DTSWizard, and I was having problems so I turned off Enforce Replicaton, Enforce FK Constraints on a couple of fields. I'm new with SQL Server so I'm not sure if this even caused the problem. (Do I need to turn these back on, or is this a Developer switch of some kind?)
The end result left me with duplicate records in the table, and I'm not able to delete any of them. This is the Error I got...
A problem occurred attempting to delete row 1.
Error Source: Microsoft.VisualStudio,Datatools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they after multiple rows(2 rows).
If someone could tell me what I need to do so I can delete the records I'd really appreciate it.
Thanks,
Bill
View 11 Replies
View Related
Mar 4, 2008
Hello Frnds....Can anybody give the answer of this question as How to Delete duplicate records from Table ?
I Know that with check option and also with Unique Constraint we can avoid to enter duplicate records in table but How to delete from table which does not have any constraints ?
View 8 Replies
View Related
Oct 21, 2014
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
[Code] ....
Delete and merge duplicate records from joined tables? I am trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
Select a1z103acno AccountNumber, a1z103frnm FirstName, a1z103lanm LastName, a1z103ornm OrgName, a3z103adr1 AddressLine1, A3z103city City, A3z103st State, A3z103zip Zip, a6z103area AreaCode, a6z103phon PhoneNumber, a8z103mail Email from proddta.fz103a1 with (nolock) inner join proddta.fz103a2 with (nolock) ON a1z103acno = a2z103acno INNER JOIN proddta.fz103a3 with (nolock) ON a2z103adid = a3z103adid and a2z103actv = 'Y' and a2z103prim = 'Y' LEFT OUTER JOIN proddta.fz103a5 with (nolock) ON a1z103acno = a5z103acno and a5z103actv = 'y' and a5z103prim = 'Y' INNER JOIN proddta.fz103a6 with (nolock) ON a5z103phid = a6z103phid LEFT OUTER JOIN proddta.fz103a8 with (nolock) ON a1z103acno = a8z103acno and a8z103actv = 'Y' and a8z103prim = 'Y'
View 2 Replies
View Related
Jul 20, 2005
Hi All,I've the following table with a PK defined on an IDENTITY column(INSERT_SEQ):CREATE TABLE MYDATA (MID NUMERIC(19,0) NOT NULL,MYVALUE FLOAT NOT NULL,TIMEKEY INTEGER NOT NULL,TIMEKEY_DTTM DATETIME NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) IDENTITY(1,1) NOT NULL)GOALTER TABLE MYDATAADD CONSTRAINT PK_MYDATAPRIMARY KEY (INSERT_SEQ)GOThe TIMEKEY_DTTM field is generated, from the value actually insertedinto theTIMEKEY field, by the following trigger:CREATE TRIGGER TIMEKEY1ON MYDATAFOR INSERT ASBEGINDECLARE @M_TIMEKEY_DTTM DATETIMESELECT @M_TIMEKEY_DTTM = DATEADD(SECOND, INS.TIMEKEY +EP.GMT_OFFSET * 0 ,'1970-01-01 00:00:00.000')FROM INSERTED INS, LOCATIONINFO EPWHERE INS.EID = EP.EIDUPDATE MYDATASET TIMEKEY_DTTM = @M_TIMEKEY_DTTMFROM INSERTED INS, MYDATA MDWHERE MD.INSERT_SEQ = INS.INSERT_SEQENDGOThere is also a composite, non unique, index defined on thetuple:(MID,IID,TIMEKEY,EID)CREATE INDEX IX_METDATA ON MYDATA (MID,IID,TIMEKEY,EID)GOAs a consequence of an application design change, I would also changethis index to be UNIQUE, but when I try to drop and create it I get anerror, because the tables stores some duplicated rows...In order to succesfully upgrade the index definition, I wrote some DMLstaementsto lookup and remove the duplicated rows, keeping only the firstrecord inserted, i.e. the one with the lowest INSERT_SEQ:---- This table stores then umber of duplicated records eventuallydiscovered-- into the MYDATA table; the initial value for the NUM_DUPLICATESfield is-- 0 (no duplicated record)--DROP TABLE DUPLICATESGOCREATE TABLE DUPLICATES (TABLENAME VARCHAR(17),NUM_DUPLICATES NUMERIC(19,0) )GOINSERT INTO DUPLICATES VALUES ('MYDATA',0)GOINSERT INTO DUPLICATES VALUES ('CATEGORIESDATA',0)GO---- ///////// CLEAN UP OF MYDATA TABLE--DROP TABLE TMP_MYDATAGOCREATE TABLE TMP_MYDATA (MID NUMERIC(19,0) NOT NULL,TIMEKEY INTEGER NOT NULL,IID NUMERIC(19,0) NOT NULL,EID NUMERIC(19,0) NOT NULL,INSERT_SEQ NUMERIC(19,0) )GO---- Insert into the TMP_MYDATA table all the duplicated records for-- the tuple (MID,IID,TIMEKEY,EID) and NULL for the INSERT_SEQ field--INSERT INTO TMP_MYDATA (MID,IID,TIMEKEY,EID)SELECT MID,IID,TIMEKEY,EIDFROM MYDATAGROUP BY MID,IID,TIMEKEY,EIDHAVING COUNT(*)>1GO---- Updates the INSERT_SEQ field to the lowest value in the group-- of duplicated records--UPDATE TMP_MYDATASET TMP_MYDATA.INSERT_SEQ = (SELECT MIN(INSERT_SEQ)FROM MYDATAWHERE TMP_MYDATA.MID = MYDATA.MID ANDTMP_MYDATA.IID = MYDATA.IID ANDTMP_MYDATA.TIMEKEY = MYDATA.TIMEKEY ANDTMP_MYDATA.EID = MYDATA.EID )GO---- Updates the value of NUM_DUPLICATES for the MYDATA table.--UPDATE DUPLICATESSET NUM_DUPLICATES = (SELECT COUNT(*) FROM TMP_MYDATA)WHERE TABLENAME = 'MYDATA'GO---- Delete from the MYDATA table all the duplicated records,-- keeping only the row with the lowest INSERT_SEQ-- The delete is performed only if there are duplicated recors;-- this is achieved using a "short circuit" AND on the number ofrecords-- stored into the NUM_DUPLICATES field of the DUPLICATES table for-- the MYDATA table...--DELETE FROM MYDATAWHERE ( SELECT NUM_DUPLICATES FROM DUPLICATES WHERE TABLENAME ='MYDATA') > 0 ANDEXISTS ( SELECT 1FROM TMP_MYDATAWHERE MYDATA.MID = TMP_MYDATA.MID ANDMYDATA.IID = TMP_MYDATA.IID ANDMYDATA.TIMEKEY = TMP_MYDATA.TIMEKEY ANDMYDATA.EID = TMP_MYDATA.EID ANDMYDATA.INSERT_SEQ > TMP_MYDATA.INSERT_SEQ )GOThis tecnique works fine on a normal table (1M recs) but is not veryperformanton huge tables (>10M records)!Do you know a better way to achieve the task of removing all theduplicates records, preserving the lowest INSERT_SEQ betwee theduplicates and also preserving the sequence seed, so that a new recordinserted at time t1>t0 is enumerated with an INSERT_SEQ|t1 >max(INSERT_SEQ)|t0 ?Thanks a lot for your help!PatrizioPS. sorry for such a large post!
View 1 Replies
View Related
Jul 20, 2005
Hi,I have an sql database that has the primary key set to three fields,but has not been set as unique(I didn't create the table).I have 1 record that has 2 duplicates and I am unable to delete theduplicate entries.If I try to delete any of the three records(they are identical) I getthe message 'key column is insufficient or incorrect. Too many rowswere affected by update'.I am trying to do this within Enterprise Mgr.Any suggestion?Thanks much
View 2 Replies
View Related
Jun 12, 2006
Hi everyone.
I have two tables: the catalog table and the detail table.
The two tables are joined by a two-columns key.
I want to make a Delete sentence for delete all the rows in the Catalog table that aren't in the detail table, in SQL Server.
The only problem is that the key is composed by two colums.
If the Key was maded of one column, that would be easy, like this:
DELETE FROM CATALOG
WHERE CATALOGKEY NOT IN (SELECT CATALOGKEY FROM DETAIL)
But it is possible to make a delete sentence if the key has two columns ?
Thanks
View 2 Replies
View Related
Oct 21, 2014
Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?
SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State
[code]...
View 1 Replies
View Related
May 19, 2012
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies
View Related
Jan 27, 2015
1.) Would you agree if I said that the MPOG_CASE_ID is the one and only primary key in this table ?
2.) I thought the PRIMARY key can not have duplicate values .... Correct me if you disagree ?
View 8 Replies
View Related
Sep 10, 1998
Hi All,
I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`.
Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`.
I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file.
TIA and you have a great day.
David Nguyen.
View 3 Replies
View Related
Aug 13, 2004
I have a Transform Data Task that copies a lot of data from my source system. Unfortunately, I cannot use a DISTINCT in the SQL from the source system, due to a very poor ODBC driver!
So, when I am creating my primary key, I am trying to do a lookup on the PK column before I insert the record to see if it exists. If it does, then I skip the row. The lookup references the target database of the task.
The problem I have is that the lookup doesn't find any duplicates loaded from the database. It allows them through and causes the database to throw a primary key error.
Has anyone experienced this, or think they know what I'm doing wrong?
View 5 Replies
View Related
Jul 6, 2007
I am trying to duplicate a row in a table and this is the solution I came up with:
CREATE PROC duplicate_row(@my_primary_key)
AS
DECLARE @sql_stmt varchar(MAX)
SET @sql_stmt = 'INSERT INTO My_Table('
SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')
SET @sql_stmt = @sql_stmt + 'title) SELECT '
SELECT@sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'My_Table'
ANDCOLUMN_NAME NOT IN ('my_primary_key','title')
SET @sql_stmt = @sql_stmt +' title+''_copy'' AS title FROM My_Table WHERE my_primary_key = '+LTRIM(STR(@my_primary_key))
EXEC(@sql_stmt)
However this stored proc is horrifically slow. I was wondering if anyone had any suggestions on a better way to accomplish this.
-thanks
View 5 Replies
View Related
Mar 14, 2008
Hi,
I get the following error when I try to insert a stored procedure in an SQL-server.
Violation of PRIMARY KEY constraint 'PK_Login'. Cannot insert duplicate key in object 'Login'.
My question if this is the real problem or the symtom of something else? I find it hard to believe that try to insert double key values. The table Login doesn´t contain any values.
Thanks in advance!
View 9 Replies
View Related
Aug 6, 2007
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
View 7 Replies
View Related
Aug 20, 2006
How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ?
Thanks in advance !
stephen
View 3 Replies
View Related
Feb 23, 2001
Hi,
I am putting my problem in an example as I Feel it would be clear.
Assume my table PEOPLE is having 4 columns with 6 rows, the SlNo being primary key.
SlNo Name LastName birthdate
1 A B x --
2 C B x |-- 1 pair (A, B, x)
3 D E y --|------------
4 A E y | |
5 A B x __| |-- 2'nd pair (D, E, y)
6 D E y ---------------
In this scenario, I need to find SlNo values having similar values in other columns. The o/p for above must be:
1
5
0
3
6
0 (0 needs to include in output for distinction in the sets)
(a)IS THIS POSSIBLE TO DO IN ONE SELECT STATEMET? and HOW?
(b)If I create another temp table tempPEOPLE and select distinct row information of the 2'nd, 3'rd and 4'th columns from the PEOPLE table and
then selecting SlNo's where the information match, I am able to get o/p
1
5
3
6
without 0...and I cannot makeout the distinct sets in this.
HOW DO I FIND THE DISTINCTION IN SETS?
Reshma.
View 1 Replies
View Related
Jul 10, 2007
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
View 7 Replies
View Related
Mar 27, 2008
I have one table that stores log messages generated by a web service. I have a second table where I want to store just the distinct messages from the first table. This second table has two columns one for the message and the second for the checksum of the message. The checksum column is the primary key for the table.
My query for populating the second table looks like:
INSERT INTO TransactionMessages ( message, messageHash )
SELECT DISTINCT message, CHECKSUM( message )
FROM Log
WHERE logDate BETWEEN '2008-03-26 00:00:00' AND '2008-03-26 23:59:59'
AND NOT EXISTS (
SELECT * FROM TransactionMessages
WHERE messageHash = CHECKSUM( Log.message ) )
I run this query once per day to insert the new messages from the day before. It fails when a day has two messages that have the same checksum. In this case I would like to ignore the second message and let the query proceed. I tried creating an instead of insert trigger that only inserted unique primary keys. The trigger looks like:
IF( NOT EXISTS(
SELECT TM.messageHash
FROM TransactionMessages TM, inserted I
WHERE TM.messageHash = I.messageHash
) )
BEGIN
INSERT INTO TransactionMessages ( messageHash, message )
SELECT messageHash, message FROM inserted
END
That didn't work. I think the issue is that all the rows get committed to the table at the end of the whole query. That means the trigger cannot match the duplicate primary key because the initial row has not been inserted yet.
Does anyone know the best way to do this?
Thanks for your help,
Drew
View 2 Replies
View Related
Apr 25, 2008
I have 3 source for IS flow. One is flat file, one is DB table and one is output bad data. It might be a situation when I could have duplicate primary key since records come from 3 sources (flat file, db table, reject (output) table). Can any one give me suggestion how to handle duplicate primary key problem in this situation.
Any suggestion will be appreciated.
Thanks..
View 8 Replies
View Related
Jun 6, 2004
We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
View 4 Replies
View Related
Apr 6, 2012
I'm trying to modify a specific row in a table.
$paper_id = 2A
$query = "UPDATE book SET paper_id='$paper_id', title='$title'";
I'm getting this error <<query failed: Duplicate entry '2A' for key 'PRIMARY'>>
Structure:
Column: paper_id
Type: Char
Length: 20
Deafult: None
View 5 Replies
View Related
Aug 9, 2006
'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?
TIA,
Barkingdog
View 4 Replies
View Related
Jun 25, 2000
I have a table which looks as follow:
field1 field2 field3 field4 field5 ......
A B C A X ......
A B C B Y ......
A B C C Z ......
A B C A Y ......
. . . . . ......
I want to delete all the rows except one row. Anybody can help?
Thank you very much.
View 3 Replies
View Related
Jan 20, 2000
How do you delete duplicate rows in a table so only one row is left in the table, using T-SQL.
View 1 Replies
View Related
