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

18
votes
1answer
24k 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 ...
8
votes
5answers
9k 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 ...
6
votes
3answers
5k 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 ...
5
votes
3answers
814 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
12k 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
2answers
836 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 ...
4
votes
1answer
359 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
758 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
2answers
555 views

MySQL check duplicate with group by using wildcard?

+----+--------------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 1 | Ramesh Olive | 32 | ...
4
votes
1answer
223 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
2answers
5k 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 ...
3
votes
5answers
2k 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
87 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
139 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
573 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
1k 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
1k 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
109 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
5k 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
286 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
1answer
54 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
2answers
114 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
175 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
2answers
122 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
1k views

Distinct rows from a join

I have two MySQL tables: ╔═══════════════════╗ ║ wpss_question_set ║ ╠═══════════════════╣ ║ id ║ ║ quesset ║ ╚═══════════════════╝ ╔════════════════╗ ║ wpss_questions ║ ...
1
vote
1answer
135 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
170 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
2answers
54 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
630 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
56 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
433 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
1answer
6k 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) ...
1
vote
2answers
5k 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
36 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
113 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
1answer
857 views

Master-Master Replication Duplicate Key

I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got MASTER 1 Configuration /etc/my.cnf [mysqld] server-id=1 bind-address ...
1
vote
1answer
71 views

Summation query with complex grouping criteria

I have a database of coded actions with this structure. {User, Date, ActionCode} I need to generate reports based on dates and action codes, and the action codes are not clean because we have to ...
1
vote
0answers
439 views

Duplicate GROUP_CONCAT values

Writing a plugin for Wordpress using a custom post type and an additional alias table to hold repetition of the post events. The problem comes with duplicate post meta values. wp_post_meta table: ...
0
votes
1answer
72 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 ...
0
votes
1answer
42 views

Move Commonly-Repeated Fields into a new Table

I'm still at the design stage for a new system, and I've noticed there are several places where different types of object share similar fields. In this case, we have customers, offices, warehouses ...
0
votes
1answer
78 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 ...
0
votes
1answer
61 views

How to avoid duplications using a function

I am using this function in a bulk insert to avoid duplication of url paths. CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4) RETURNS "int4" AS $BODY$ DECLARE ...
0
votes
2answers
662 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
1answer
12 views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
0
votes
2answers
33 views

Duplicate databases error

I have two Oracle 11gR2 databases on separate servers. I want to duplicate these databases. For this, I do these steps : In auxiliary db : create password file with password that is same if ...
0
votes
2answers
72 views

SQL Server De-duplication Trigger

I have a table that contains columns of Country, City, Location, Description. Due to the size of the description field it's not possible to bind the four fields into a composite key to prevent ...
0
votes
1answer
57 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
0
votes
2answers
386 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 ...