Find rows with 2 fields are duplicate

Databases
1

How can i identify rows where column a and column b in both rows are the same and then delete one of them?

e.g.:

row 1: column a=6 column b=4
row 5: column a=6 column b=4

delete one of these rows.

cheers

monkey

2

i think you may be surprised at how closely the SQL fits the requirement you just stated

SELECT CommunicationAuditID
  FROM Link_ContactCommunications 
 WHERE ContactID = 9
   AND CommunicationAuditID NOT IN
       ( SELECT CommunicationAuditID
           FROM Link_ContactCommunications
          WHERE ContactID = 37 )

there is also an alternative method –

SELECT CommunicationAuditID
  FROM Link_ContactCommunications AS x
LEFT OUTER
  JOIN Link_ContactCommunications AS y
    ON y.CommunicationAuditID = x.CommunicationAuditID
   AND y.ContactID = 37
 WHERE x.ContactID = 9
   AND y.CommunicationAuditID IS NULL
3

smarter application code

4

aaaargh :blush:

i’m sorry again, that sql works only in mysql (which you might’ve noticed in the quote i posted from daManual)

you’re obviously using SQL Server

(aside: please always identify your database system in your first post in this forum)

could you script out your table please, i’d like to see all the “columns with different values as well”

5

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘IGNORE’.

6

i’m sorry, i left out an important part —

ALTER IGNORE TABLE daTable
ADD UNIQUE ( columna, columnb )

and of course make sure you take a backup first

7

There are columns with different values as well, including a unique id - I think this is causing the ALTER TABLE suggestion to not work?

8

ALTER TABLE daTable
ADD UNIQUE ( columna, columnb )

and of course make sure you take a backup first

:slight_smile:

9

All columns have the same value? Or are there columns in these rows that have different values?

10

Great - cheers! I thought it was something to do with NOT IN, but i’d never used that before!

Thanks again.

11

Ok - rethink. How would i return a list of all rows where contactID = X and CommsID does not appear in a list of records where contactID =Y?

That way i can update this list and then delete any conact x whic remain.

cheers

12

The problem is that there are times when i need to merge two contacts together and in doing so, set all communications to the new contact (which is actually one of the two that are merging). The trouble is that both of the merged contact may have a row pointing to the same communicationAuditID, so when i set the contact id of one of the merged to the other, this would create a duplicuate which trips the update - this is why i removed the need for the 2 columns to be unique, hoping that i could delete any duplicated after the update - does that make sence? How can i get around this?

Also, this is the simpler of the two tables, the other contains several other rows which are not relevant to the update, but should not create a complete duplicate record.

cheers

13

it looks like there aren’t any “columns with different values as well”

there’s only the identity column, which, in this particular table, is totally useless

if you were to drop it, and make the other two columns the primary key, your duplications would disappear

CREATE TABLE dbo.Link_ContactCommunications
( ContactID            INTEGER NOT NULL
, CommunicationAuditID INTEGER NOT NULL
, CONSTRAINT PK_Link_ContactCommunications 
    PRIMARY KEY CLUSTERED 
     ( ContactID
     , CommunicationAuditID
     ) 
)
14

Sorry my bad! Here is one of the tables in question:


USE [MYDB]
GO
/****** Object:  Table [dbo].[Link_ContactCommunications]    Script Date: 07/12/2010 16:51:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Link_ContactCommunications](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ContactID] [int] NOT NULL,
	[CommunicationAuditID] [int] NOT NULL,
 CONSTRAINT [PK_Link_ContactCommunications] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]