Delete And Merge Duplicate Records From Joined Tab
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
ADVERTISEMENT
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
Jun 6, 2007
Hello,
I have the following Query:
1 declare @StartDate char(8)2 declare @EndDate char(8)3 set @StartDate = '20070601'4 set @EndDate = '20070630'5 SELECT Initials, [Position], DATEDIFF(mi,[TimeOn],[TimeOff]) AS ProTime6 FROM LogTable WHERE 7 [TimeOn] BETWEEN @StartDate AND @EndDate AND8 [TimeOff] BETWEEN @StartDate AND @EndDate9 ORDER BY [Position],[Initials] ASC
The query returns the following data:
Position Initials ProTime -------------------------------------------------- -------- ----------- ACAD JJ 127 ACAD JJ 62 ACAD KK 230 ACAD KK 83 ACAD KK 127 ACAD TD 122 ACAD TJ 127
What I'm having trouble with is the fact that I need to return a results that has the totals for each set of initials for each position. For Example, the final output that I'm looking to get is the following:
Postition Initials ProTime
ACAD JJ 189ACAD KK 440ACAD TD 122ACAD TJ 127
Any assistance greatly appreciated.
View 3 Replies
View Related
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
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
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
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
Dec 20, 2006
How do I delete from a joined table....
I want to delete all the records from transaction AND order table where status is > 4
sumfing like:
DELETE ORDERS.*, TRANSACTIONS.*
from ORDERS
INNER JOIN TRANSACTIONS
where ORDERS.order_no = TRANSACTIONS.order_no
and status > 4
but doesnt seem 2 like dat???
THANKS :)
View 5 Replies
View Related
Oct 1, 2013
I have 2 tables:
Table 1 Customers with column CustomerId
Table 2 Purchases with columns PurchaseId and CustomerId
I want to select all customers who have made just 1 purchase, what would be the sql for that? I know I can join the tables to get the customers with purchases, but I don't know how to limit it to those with just 1 purchase (and exclude all the other customers).
View 9 Replies
View Related
Jan 18, 2005
I've searched high and low for info on how to do this... If anyone has an idea, I'd really appreciate it.
I have three tables: PEOPLE, PROJECTS, COMMENTS. I want users from the PEOPLE table to retrieve a list of PROJECTS and be able to add/edit COMMENTS on those projects.
The tables look like:
PEOPLE
people_id (primary key)
first_name
last_name
PROJECTS
projects_id (primary key)
project_title
project_summary
COMMENTS
comments_id (primary key)
projects_id (foreign key)
people_id (foreign key)
comment_detail
I'd like to be able to output something like what I have below, but I don't know how to loop over the comments/people within the select statement for the projects.
DESIRED OUTPUT
Project #1
Comment #1 by Person #1
Comment #2 by Person #3
Comment #3 by Person #8
Project #2
Comment #1 by Person #2
Comment #2 by Person #3
Comment #3 by Person #6
Etc...
I've done it before by just listing the projects and then providing a detail page with all the comments, but it's much less confusing to access all the comments from the same page, grouped by project.
Thanks in advance.
View 1 Replies
View Related
Nov 7, 2006
I'm using SQL server 200
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
---------------------------------------------------------------------------------------------------------------------------------
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
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
Feb 1, 2007
Hi seniors
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
regards
Ahmad Drshen
View 6 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
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
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
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
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
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
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
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
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
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
