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 (1)

22
votes
1answer
38k 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
4answers
17k 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
12k 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 ...
9
votes
3answers
63k views

select rows where column contains same data in more than one record

I have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record. Here's ...
9
votes
4answers
3k views

How to check if a subquery has exactly one distinct result and a specified value concisely?

I found myself writing the following: select 'yes' where exists(select * from foo where val=1) and not exists(select * from foo where val<>1); and wondering if there is a more concise way ...
8
votes
1answer
19k 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
957 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
1answer
602 views

Percona MySQL 5.5 Unique key is duplicated

I'm totally out from ideas, so maybe somebody else could answer my question. We have a MySQL 5.5 server from Percona with high traffic. The application is in PHP and writes always to master. We have ...
5
votes
2answers
1k views

Partial db dump/restore

A part of my database (PostgreSQL 9.3) relies on extra tables (ex. County, City, Town, ... ). I don't manage these tables, they are updated regularly by a third party. Each time I get a new full dump, ...
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
807 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
2answers
1k views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
4
votes
1answer
594 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
2answers
656 views

Duplicate database leaves orphan archivelog files

I am cloning the production database to another one, lets say CLONEDB, in a regular basis. I am using rman duplicate database script like: run { duplicate target database to CLONEDB FROM ACTIVE ...
4
votes
1answer
912 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
256 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
212 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
2answers
4k views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY ...
3
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 ...
3
votes
1answer
141 views

Consolidating 2 Tables with same Values

I am working on a project trying to consolidate two databases, DB1 and DB2, for my organization into a single database, DB3. Initially our organization started with DB1. A few years back they made a ...
3
votes
3answers
78 views

Remove duplicates in sequence based on keys

I am looking to remove duplicates on a row by row comparison based on keys Member and Group and data sorted ASC on Member, Group and StartDate. Just return the records in the select query and not ...
3
votes
1answer
118 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
1answer
37 views

Oracle 12c Rman Duplicate Archive Log Not Verified

I am trying to duplicate a DB from live to dev but it is failing. Can anybody advise what is going wrong? Both databases are running Oracle 12c. All database files seem to be copied over but it fails ...
3
votes
2answers
44 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
52 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
221 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
4k views

Insert into … (…) (Select…) on duplicate key update

Can you help me to solve a problem. The task is to insert a record in MySQL database. terms: If the record exists - to update the record. If the record does not exist - to create a new one. ...
2
votes
2answers
5k views

Master - Master replication Duplicate entry

I've got a Master - Master replication setup and it worked fine for a couple of days but I keep getting: Duplicate entry errors after a few days... My setup is as follow: One server in The ...
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
104 views

How do I remove duplicate records in a table?

I have a table table1 with 1 millions rows of data. I want to remove all the duplicate records in the table1 I was looking at this link: How do I remove duplicate records in a join table in ...
2
votes
2answers
666 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
3answers
4k 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
1answer
2k views

Using EXCEPTION to ignore duplicates during bulk inserts

In pq 9.3.5, I'm importing records from an external source where duplicates are VERY rare, but they do happen. Given a readings table with a unique compound key on (real_time_device_id, recorded_at), ...
2
votes
1answer
24 views

De-duplication method for fields that are identical except for Unique ID

I'm looking for a decent de-duplication method. Basically I have a table which has five fields plus a Unique ID (key) field. I've run a duplicate which detection query which finds any records where ...
2
votes
1answer
24 views

Duplicating rows but incrementing the ID each time

I have the strenuous task of merging multiple Magento databases together and I am importing hundreds of blocks into the main database but subsequently have to go through every one and change the store ...
2
votes
2answers
92 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
97 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
226 views

Merge duplicate rows into one row

I have a table like below in MySQL: +-----+------+-------+-----------+----------+---------+ | #id | NAME | empid | c_lan | Java_lan | Dot_lan | ...
2
votes
1answer
486 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
348 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
39 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
30 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
75 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
47 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
538 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 ...