Slective Duplicate Record Delete/Identify Question

Feb 7, 2005

HI, I have a table similiar to this:

Division Type Section Location ModificationDate
------- ----- ------- -------- ---------------
3 4 2 Los Angeles 2/1/05
3 4 2 New York 2/4/05
4 5 1 Los Angeles 2/4/05
3 4 2 Seattle 2/7/05
4 5 1 Dallas 2/6/05
3 4 4 London 2/3/05

I need to remove duplicate records that have the same division,type,section pair by slected the most recent modification date and keeping the data in the rest of the columns. The results of what I want to do would look like:

Division Type Section Location ModificationDate
------- ----- ------- -------- ---------------
3 4 2 Seattle 2/7/05
3 4 4 London 2/3/05
4 5 1 Dallas 2/6/05

Does anyone have idea how I would do something like this? Thanks.

View 4 Replies


ADVERTISEMENT

Delete Duplicate Record

Oct 23, 2006

Hi ,

How can i delete the duplicate record from a table

use Northwind
create table Emp (Ecode char(2), Ename char(10))
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A4','D')
Insert into Emp(Ecode, Ename) values('A4','D')

select * from emp order by Ecode


Thanks

ASM

View 7 Replies View Related

Identify Potential Duplicate

Apr 22, 2015

There is one report to identify potential duplicate in a table and it is performing poor.I'm now tuning the existing SP and got struck in modifiying it. rewrite the query in a best way. I just pasted below an example of query which is now in a report.The report will be run every week currently the table has 10 million records, and every week there will 5k to 10k will be added up so with that 5k to 10 k we have to check all the 10 miilion rows that if it is duplciated the logic is (surname = surmane or forename = forename or DOB =DOB )

Create table #employee
(
ID int,
empid varchar(100),
surname varchar(100),
forename varchar(100),
DOB datetime,
empregistereddate datetime,
Createdate datetime

[code]...

View 7 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

Trying To Identify A Unique Record On Two Separate Tables

Oct 31, 2007

Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index. 
Contents                                   RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P. 
 
 
 
 

View 8 Replies View Related

T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table

Sep 15, 2014

I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.

View 4 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View 3 Replies View Related

Duplicate Record

Jan 30, 2006

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

I’m not sure how to write this query, can someone help? Thanks in advance!

View 3 Replies View Related

Duplicate Record

Jan 14, 2007

Hi guys how do you hide duplicate records, how would I do a select statement for that

In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )

I have about had it with this database I have been asked to make a report out of

View 14 Replies View Related

Delete Duplicate Records

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

Delete Duplicate Rows

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

Delete Duplicate Rows Using T-SQL

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

Delete Duplicate Records

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

DELETE DUPLICATE ROWS

Mar 30, 1999

CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS
IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW
TO GET YESTERDAY DATE BY USING ISQL WINDOW.



THANKS
JK

View 2 Replies View Related

How Can I Delete All But One Instance Of A Duplicate Row?

Oct 7, 2004

I have several rows in a table that contain duplicate information. I want to trim them down to one instance per row, without other duplicates.

I have several columns, but only 2 of the columns are used to determine the "Identical Rows", however one of the other columns could also be used to determine which of the others are deleted.

Let's just say I have a table called DATA.
In DATA I have the following columns:
C1: ID/Key column (Unique numbers not associated with any other row)
C2: Specific Data column 1
C3: Specific Data Column 2
C3: Keeper if not null
C4: Misc Info
C5: Misc Info

Basically IF C2 & C3 are duplicated, then I want to keep one of these and ditch the others.

The row I want to keep is the one that has info in C4, but at times C4 does not have anything in it on any of them, at that point I just care about keeping one of the C2 & C3 Duplicate rows.

The other issue I face is C2 will always have something in it, but at times C3 will not and C4 will.

So I want to keep the one duplicate row that has the most info in it. At minimum, I want to always keep C2 & C3 if they both contain data.

For example, if I had the following duplicate rows:
(C2) red (C3) 123 (C4) XYZ
(C2) red (C3) 123 (C4) XYZ
(C2) red (C3) 123 (C4) XYZ
(C2) red (C3) (C4) XYZ
(C2) red (C3) 123 (C4)

I want to keep: (C2) red (C3) 123 (C4) XYZ

I have some rows that have three exact duplicates, some that have four, some that have two.

How can I tell the Query Manager to delete all but one instance of each duplicate row?

I did do a query to find out how many duplicates I have and found it to be around 232,000+. Basically I will be cutting this in half if I can get rid of the duplicates.

I have read 30 different ways on many sites and I am now officially confused

Thank you for your help.

View 6 Replies View Related

Delete Duplicate Rows

Jan 13, 2005

Suppose that we have the following rows in a MSSQL table :


1administrateur1NULLNULL
2administrateur2NULLNULL
1administrateur1NULLNULL
2administrateur2NULLNULL



How to remove duplicates (leave only the 2 first rows) ?

View 1 Replies View Related

Delete Duplicate Rows

Oct 17, 2005

Hi,
I have the following query to select duplicate rows from the table. How can i delete them with out using temp table.

select UserName, Title, Name, ColWidth, Sequence
from table1 (nolock))
Group by UserName, Title, Name, ColWidth, Sequence
Having count(*) >1


Any help would be greately appreciated.
Thanks

View 2 Replies View Related

Delete Duplicate User

Apr 30, 2008

Hello I am import a file into a SSIS package. Is there a easy way to delete duplicate user in the file. Could i use a sql task or something.

thanks

View 1 Replies View Related

Duplicate Delete Timeout

May 2, 2008

Hi All,

I am trying to delete the duplicates from a table, but keep 1 of the records.

Here is my query which works on smaller tables <2000 records, but the query either timeouts or just runs and runs on larger tables >50,000 and >150,000.


DELETE
FROM Table1
WHERE Table1.ID IN

/* LIST 1 - all rows that have duplicates*/
(SELECT F.ID
FROM Table1 AS F
WHERE EXISTS ( SELECT computerName, programName, version, publisher, Count(ID)
FROM Table1
WHERE Table1.computerName = F.computerName
AND Table1.programName = F.programName
AND Table1.version = F.version
AND Table1.publisher = F.publisher
GROUP BY Table1.computerName, Table1.programName, Table1.version, Table1.publisher
HAVING Count(Table1.ID) > 1))
AND Table1.ID NOT IN

/* LIST 2 - one row from each set of duplicate*/
(SELECT Min(ID)
FROM Table1 AS F
WHERE EXISTS ( SELECT computerName, programName, version, publisher, Count(ID)
FROM Table1
WHERE Table1.computerName = F.computerName
AND Table1.programName = F.programName
AND Table1.version = F.version
AND Table1.publisher = F.publisher
GROUP BY Table1.computerName, Table1.programName, Table1.version, Table1.publisher
HAVING Count(Table1.ID) > 1)
GROUP BY computerName, programName, version, publisher);

TABLE STRUCTURE::
ID computerName programName version publisher installDate
1 COMP1Microsoft .NET Framework 1.11.1.4322Microsoft 20050216
2 COMP1Windows XP Hotfix - KB89178120050110.17Microsoft NULL
3 COMP1Windows Media Player 10 NULL NULL NULL
4 COMP1Microsoft .NET Framework 1.11.1.4322Microsoft 02/16/2005

The duplicate records i am trying to delete are not truly duplicates, each record has a unique ID and install dates could vary. I am trying to identify duplicate records as identical (computerName + programName + version + publisher) See records 1 and 4 in table structure above... i dont care which of the duplicates is kept, as long as 1 is kept.

What I am looking for is either a completely new query which maybe optimized to run on a larger scale table or maybe just a performance tweak to my exisitng query??

Any help would be greatly appreciated!

Thanks.

View 3 Replies View Related

Delete Duplicate Records

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

How Can I Delete Duplicate Records

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

Delete Duplicate ROWS

Jun 18, 2008

How to delete duplicate records from table ? Is there any query for that ?
I am using SQL 2005.

View 3 Replies View Related

Delete Duplicate Records

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

Delete Duplicate Rows

Jan 27, 2014

declare @a1 table
(id int not null identity(1,1),
phone decimal(18,0),
adress nvarchar(100))
insert @a1
(phone,adress)

[Code] ....

id phone address
----------- --------------------------------------- ----------------------------------------------------------------------------------------------------
1 111 new york
2 111 new york
3 111 new york
4 222 maxico
5 222 mexico

select phone,count(phone) as say from @a1 group by phone having count(phone)>1

phone say
--------------------------------------- -----------
111 3
222 2

How can I remove duplicate phone. For example

after delete
select*from @a1
select*from @a1

id phone address
----------- --------------------------------------- ----------------------------------------------------------------------------------------------------
1 111 new york
4 222 maxico

I wrote this to show for example in my real table have 50000 rows and 1751 duplicate rows.

[URL] .....

View 2 Replies View Related

How Do You Delete Duplicate Rows......

Jan 18, 2006

How do you delete duplicate rows when there are constraints or like in professional terms as we say "Referential Integrity".

Mr.Madhivanan i hope you remember me. Because it is after all purpose that binds us all together...purpose...it is inevitable Mr.Madhivanan.

View 8 Replies View Related

Duplicate Record Trigger

Nov 24, 2006

This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.
 
            -- duplicate record check
            SET @step = 'Duplicate record'
            IF EXISTS (   
                        SELECT     i.myID, i.Type
                        FROM         INSERTED i INNER JOIN
                                              myDB.dbo.myTable c ON i.myID = c.myID
                        GROUP BY i.myID, i.Type
                        HAVING      (COUNT(*) > 1) AND (i.Type = 'In')
            )
            BEGIN
                        ROLLBACK transaction
                        RAISERROR('Error: step: %s.  rollback is done.', 16, 1, @step)
                        Return
            END
           
What is problem?
 

View 1 Replies View Related

Duplicate Inserted Record

Nov 14, 2007

Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT    CompanyName:    <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>"    insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)"    selectcommand="SELECT * FROM [items]">    <insertparameters>        <asp:controlparameter controlid="txtCompanyName" name="CompanyName" />        <asp:controlparameter controlid="txtPhone" name="Phone" />    </insertparameters></asp:sqldatasource> VBPartial Class Default2    Inherits System.Web.UI.Page    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click        SqlDataSource1.Insert()    End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items   

View 2 Replies View Related

Duplicate Record Question

May 4, 2004

In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.

The problem is what to actually test for. When the code throws the exception it is a big long string . .

"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"

I just thought that there has to be something simplar to test for than comparing the exception to the above string.

Can anyone tell me of a better way of doing this ?

(by the way I am only using Web Matrix and MSDE in case it matters)

Mark

View 2 Replies View Related

Duplicate Record Problem

Nov 4, 2005

I am working on a web application that utilizes a sql server database.  One of the tables is a large text file that is imported through a DTS package from a Unix server.  For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table.  I need to get rid of these duplicates, but can't seem to get a workable solution.  the query that is needed to get the records is:SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,  tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,  tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,  tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTimeFROM  tblAppointments CROSS JOIN               tblPTDEMO2WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)AND tblAppointments.Insurance1 = 'MED'AND tblAppointments.ApptTypeID <> 'MTG'AND tblAppointments.ApptTypeID <> 'PNV'AND DateDiff("dd", ApptDate, GetDate()) = 0Order By tblAppointments.ApptDateMy first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query.  My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails.  I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own.  I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing.  Any help on this topic would be greatly appreciated.  Thanks.

View 5 Replies View Related

Remove Duplicate Record

Jul 27, 2000

i'm a newbie to sql , anyone can give me suggestions on how to
remove duplicate records in a table, a table also has primary key,
thanks

View 1 Replies View Related

Duplicate Record Problem

Apr 23, 2008

So I'm working on updating and normalizing an old database, and I have some duplicate records that I can't seem to get rid of. Every column is identical, right down to what is supposed to be the key. I can't right a delete query to just isolate one row, and I can't delete (or even udpate) any row in management studio. Any thoughts on how to remove the extra rows?

There is a field that's supposed to be unique, so I can write a simple query to get all of the problem rows. The only thing is that they come back in pairs.

View 2 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







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