The Duplication tag covers the topic of duplicated data and the strategies involved in removing the duplicates, often referred to as deduplication or simply dedupe.

learn more… | top users | synonyms

19
votes
1answer
33k views

Optimal way to ignore duplicate inserts? [duplicate]

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table ...
10
votes
3answers
12k views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
10
votes
5answers
11k views

How do I copy my SQL Azure database to a local SQL Server instance?

I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting ...
7
votes
1answer
17k views

On duplicate key do nothing

I am inserting into the following table using LuaSQL with PtokaX API. CREATE TABLE `requests` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ctg` VARCHAR(15) NOT NULL, `msg` ...
5
votes
3answers
899 views

Database replication and consistency check

I got a question about 2 databases (oracle 10g) that I have, let's call them A and B. A have some information(in various tables) and I want B to get a partial copy of some tables from A, and ...
5
votes
2answers
1k views

Resolving DUPLICATE for a column with UNIQUE

I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test ...
5
votes
2answers
719 views

MySQL check duplicate with group by using wildcard?

+----+--------------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 1 | Ramesh Olive | 32 | ...
4
votes
2answers
7k views

How do I remove duplicate records in a join table in PostgreSQL?

I have a table that has a schema like this: create_table "questions_tags", :id => false, :force => true do |t| t.integer "question_id" t.integer "tag_id" end ...
4
votes
1answer
521 views

MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

I have a normalized database and I'm trying to return data from multiple tables using JOINs and GROUP_CONCAT. Problem: Rows are being duplicated with GROUP_CONCAT. I can't use DISTINCT because some ...
4
votes
1answer
873 views

Select one name per person where spelling variations exist

I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
4
votes
1answer
240 views

Need to find duplicate entries

I received a database with a few million records in it, but apperently there might be duplicate records in them. A user enters data into the database and a primary key is generated, however if the ...
3
votes
3answers
204 views

Selecting only one duplicate

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would ...
3
votes
5answers
3k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
3
votes
1answer
113 views

Storing statistic results

I'm doing a small database for statistics, it's a really simple statistic like calculating the cost of an event and divide it by the amount of people that went to get the cost per attendee, but it can ...
3
votes
1answer
2k views

Getting duplicate primary key for auto increment column

I have a MySQL Innodb table with 'id' as auto increment primary key column. My Scripts are doing following operations: insert ignore into tablename set x='a', y='b' update ignore tablename set ...
3
votes
2answers
35 views

How to prevent duplicates while storing result from different application

For my case multiple application uses data from db based on date, this data is date specific so if for a date data exist application should just read it otherwise it should fetch information from ...
3
votes
1answer
48 views

What is the correct way to map relationships between structurally similar comments tables and commentable entities?

I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments. ...
3
votes
2answers
189 views

How do I deal with items that I need to repeat monthly?

I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every ...
2
votes
2answers
640 views

Duplicating a Database with RMAN

I would like to duplicate to the same server Oracle Database with RMAN. I've configured flash recovery area for Oracle Database. My database is using SPFILE as shown below. SQL> SELECT ...
2
votes
1answer
8k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) ...
2
votes
1answer
2k views

database duplication VS database replication

Is there a difference between duplication and replication with reference to a database? I am not sure if I am correct at all, but duplication means to me that we are copying bit by bit without ...
2
votes
3answers
3k views

Find duplicate records across one-to-many relationship tables

Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly. ...
2
votes
2answers
82 views

Merge two huge tables keeping only the unique rows

I have two tables: table1 with 61 million rows and table2 with 59 millions rows. The columns in both are the same (name and type). Both are imported from backup files. I want to merge these two ...
2
votes
2answers
76 views

Nested table hierarchy: foreign key duplication vs. join

I have a nested table hierarchy, similar to the following one (this is an example but demonstrates the schema well enough): +---------+ +---------+ +---------+ +-------------+ | users | | ...
2
votes
1answer
37 views

Should I create many ID for a same Person/entity in my DB?

In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, ...
2
votes
1answer
415 views

Remove duplicate records from table, leaving one copy

I have inherited a MySQL table from one of my colleagues, and am in need of a little advice. The table has the following structure: | PHONE_NO | ACC_NO | GET_MAIL | DATETIME_CRE | INT_ACC ...
2
votes
2answers
6k views

remove duplicate rows in mysql table that does not contain primary key

I have a table item having just one column name:- name ----- toys shirt mobile Shirt speaker Toys .... .... I am trying to delete duplicate rows except one duplicated record with the help of below ...
2
votes
1answer
329 views

Check against multiple duplicate records

I have data that i should insert into table. But before inserting i need to check against duplicate records and report list of those records. Table: CREATE TABLE `test` ( `A` varchar(19) NOT NULL, ...
2
votes
0answers
38 views

Join duplicates on T-sql [closed]

Background: This is largely a learning exercise. I am working on a task scheduling program. Part of the program requires making a report to print out. I am working with the original version of the ...
2
votes
0answers
26 views

MS Access database returns multiple entries for same person with more than one child

I know this is a very basic question, but I've watched about six Access database videos and still can't find the answer. I'm a volunteer creating a membership database for a church in MS Access. The ...
2
votes
1answer
67 views

Marking duplicate records

I have a list of things, some of which are duplicates. create table things ( id int primary key, title text not null, author text not null, duplicate_of int null references things(id) ); ...
1
vote
1answer
43 views

PostgreSQL, how to extract all the possible couples of a primary key in a table?

I would like to find out a PostgreSQL command that reads the content of a table, and returns all the possible couplings of the primary key of the table. Something like this: input_table (ID is the ...
1
vote
2answers
410 views

Delete duplicates with specific columns as identifier

I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512)).. My amateur-ish SQLfoo lead me to this non-functioning ...
1
vote
1answer
260 views

Check if query will cause duplicate row

I have a database with a primary key on the first column (words) I want to trim the trailing and leading ends of my data, but I get an error that this causes duplicate data EX: ' David ' will become ...
1
vote
1answer
843 views

SQLite3 - remove duplicates by multiple columns

I'm looking for the most efficient way to remove duplicates from a table where id's are unique but there are equal rows when you check other columns. Example: id name age x 1 peter 25 ...
1
vote
2answers
151 views

Duplicates while doing multiple joins

I have 4 MySQL tables 1. companies - {id*, company} 2. employees - {id*, fname, lname, id_company*} 3. technologies - {id*, technology} 4. company_technologies - {id_technology*, id_company*} Field ...
1
vote
2answers
2k views

Distinct rows from a join

I have two MySQL tables: ╔═══════════════════╗ ║ wpss_question_set ║ ╠═══════════════════╣ ║ id ║ ║ quesset ║ ╚═══════════════════╝ ╔════════════════╗ ║ wpss_questions ║ ...
1
vote
1answer
157 views

How can I achieve a unique constraint with two fields? [duplicate]

I have a table with e.g. Name and IsDeleted fields. I want to add a row constraint so that only one Name value can have IsDeleted as 'false'. There can be many duplicate Name values, but they must all ...
1
vote
1answer
175 views

MySQL detect duplicate human names [closed]

I've been tasked with identifying a solution to help identify possible duplicates when inserting new user records into a MySQL database. Does anyone know of any cheap, preferably free, solutions? The ...
1
vote
1answer
1k views

Duplicate entry in mysql slave

I have a mysql master-slave replication setup. when I checked "show slave status", I got the result as "Slave_Sql_running:No" and Last error as "Duplicate entry 'x' on query." But when I tried to ...
1
vote
1answer
42 views

Sum currencies excluding duplicatied records

I have a CRMOffer table containing customerID and other CRMOffer data. And a Sell table containing customerID and sellValue and sellPK. Sell table has a explicit PK, CRMOffer sadly doesn't have one ...
1
vote
1answer
319 views

How to remove duplicates and keep the one with the most additional information

I have a postgres table (postgres version 9.3.4) of addresses with duplicate addresses: Column | Type | Modifiers ...
1
vote
2answers
60 views

RMAN backup set, nothing else, how can I duplicate?

I have what appears to be an RMAN backup set, it has been multiplexed in 32 files, each containing 2-3 dbf's inside. The backupset refers to an SPFILE file, but it was not included in the set, the ...
1
vote
1answer
2k views

Remove all records with duplicates in db2. (Not just the duplicate records)

How can I remove all the records with duplicates in db2. I have looked at various answers but they only remove the duplicates leaving one record from that set in the table. This is what I found ...
1
vote
2answers
76 views

Preventing duplicate data when usual normalization only makes sense for some of the records

Background I have two tables, one called issues and the other called status_checks, which both contain (among others) the field comment (VARCHAR). An issue is raised by a user when something needs ...
1
vote
1answer
732 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
1
vote
2answers
6k views

Deleting duplicates with group by and count

What is the fastest method to convert the following query: SELECT COUNT(*) as c FROM tbl_fields WHERE fieldnotes IS NULL GROUP BY fieldno,fieldserial,id,fielddate,fieldsid HAVING COUNT(*) > 1; ...
1
vote
1answer
112 views

Merge duplicate rows into one row

I have a table like below in MySQL: +-----+------+-------+-----------+----------+---------+ | #id | NAME | empid | c_lan | Java_lan | Dot_lan | ...
1
vote
1answer
187 views

Error while duplicating Oracle database after failover, using 11.2.0.4

I've recently updated oracle from version 11.2.0.1 to 11.2.0.4 and my redundant system is no longer able to recover from failover. I'm using scripts both on primary and standby node to get back to a ...
1
vote
2answers
751 views

How do I find duplicate values in multiple columns over different rows?

I have a database with a table of games (some columns omitted): gameid hometeam awayteam `date` Sometimes games are added with the same home and away teams, but on a different date that is just 1 ...