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)

0
votes
1answer
183 views

duplicate entry issue with large mysql database table

I have a very large my sql database table. The other day, for no apparent reason, I now keep getting :- Duplicate entry '148245' for key 'PRIMARY' If I remove the entry at with primary of 148245, ...
0
votes
1answer
103 views

Delete duplicate with same ID PHP/PDO/MYSQL

I'm new to databases and I need your help I'm working on a website and I have few problems. I'm using PDO. There is a user table, with unique ID, name and password. User enters his username and ...
0
votes
1answer
33 views

Grouping operator in DBMS (γ) is duplicate-impervious. Why?

I'm reading Database Systems by Ullman where its mentioned that γ is duplicate-impervious. I don't understand why a simple group operation should cause elimination of duplicates. Aliter, ...
0
votes
1answer
3k views

Oracle Duplicate Database with no archived logs

I am trying to perform a duplicate operation from one server to another (testing disaster recovery) on oracle 11g. I am very much still learning about Oracle. The system is not in production, it is ...
1
vote
1answer
39 views

Exclude All Matching Rows

I have a table that has a unique ID, name, email, and some other fields. I want to match this table against another table on email, but sometimes emails are shared. The unique ID is not shared among ...
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 | ... | ...
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 ...
1
vote
1answer
31 views

Fast DELETE with double subquery on big table for dupe remover

I have a table with 9million rows. At some point in time i changed unique key from one column to two columns(for partitioning). After this i created duplicates of my first column which is unwanted, ...
0
votes
2answers
350 views

Make Oracle backup files available for duplicate

I have a Red Hat Enterprise Linux server with Oracle 10.2.0.5 and a database on it (SID is TNATEST). I have a remote CentOS server with Oracle 10.2.0.5 on it that I want to duplicate TNATEST to. The ...
1
vote
2answers
199 views

what is the quickest way to delete duplicate rows?

I need to delete duplicate rows from a large table. what is the best way to achieve that? currently I use this algorithm: declare @t table ([key] int ) insert into @t select 1 insert into @t ...
1
vote
1answer
56 views

Finding list of Duplicate records from 2 tables in DB2

I have a Person table and Address Table. Their relationship is Person.AddrId = Address.Id Person table has First_name, Last_name, and AddrId. The Address table has Address1, Zipcode, and Id. I ...
1
vote
2answers
39 views

Foreign keys update automation

I'm on SQL Server 2014 and I need to clean a table from duplicates. Problem is: if a duplicate has references in other tables, that references should be updated to reference the chosen non-duplicate ...
0
votes
1answer
39 views

Flagging duplicates

I have a huge table with a couple of duplicates: I want to add a new column that shows some value when duplicates occur, something like: case when fist.firstname = last.firstname then 'yes' else ...
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 ...
1
vote
3answers
168 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
44 views

How can I merge duplicate rows in a single table?

I have a table with duplicate barcodes (barcode) and carts (cart) and (Quantity_counted_IN) and (Quantity_Counted_out). I want to merge all duplicate records that have the same barcode and cart ...
1
vote
1answer
31 views

Show only duplicate values

How to select rows with duplicate values (non-distinct rows) and summarize with a count of the number of duplicate values in order of the most frequent duplicates? Given a table of: person_ name_ ...
3
votes
3answers
72 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 ...
1
vote
2answers
73 views

Updating duplicate records with different data

I have a migration specific issue. Data from a large table has been migrated into another table, but data for one destination column is missing due to certain issues (that are not specific to this ...
1
vote
0answers
48 views

Finding Duplicate URLs

The following URL varchar(2000) row holds URL values in a 3rd party database that I'm accessing directly, i.e. going around the HTTP API. MySQL [database]> desc TABLE; ...
1
vote
1answer
395 views

MySQL RDS Error 1062

Normally we can skip 1062 duplicate error but in rds can we skip multiple 1062 errors in one go. For example we define slave skip error =1062 in my.cnf and restart slave. but in RDS MYSQL how can we ...
10
votes
3answers
15k 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 ...
3
votes
3answers
210 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
2answers
42 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 ...
0
votes
1answer
35 views

Removing duplicate rows and changing related foreign keys in PostgreSQL

I have found all duplicate rows and now want to delete them after I update related foreign keys in other tables so they point to the correct row. How do I get a list of foreign keys related to my ...
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
90 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 ...
1
vote
1answer
288 views

SQLite3: Remove duplicates

Given the following table CREATE TABLE `sms` ( `_id` INTEGER, `thread_id` INTEGER, `address` TEXT, `person` INTEGER, `date` INTEGER, `protocol` INTEGER, `read` ...
0
votes
1answer
134 views

Row locking or avoiding duplicate insertion on primarykey/otherfield combo

I have a table that stores additional data pertaining to files/images saved in files elsewhere. Each entry can be associated with another type in the system. For users there may be one or more ...
2
votes
2answers
91 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 | | ...
1
vote
1answer
44 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
1answer
52 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 ...
3
votes
2answers
3k 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
49 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. ...
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
29 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 ...
1
vote
2answers
915 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 ...
0
votes
0answers
109 views

(My)SQL: management and duplication of multiple sub-databases [closed]

I have a pretty simple question, but it seems that there are controversies as to how to solve it in the best possible way. Say I have a web app that makes online surveys. My challenge is that each of ...
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, ...
1
vote
1answer
182 views

Optimizing Queries HAVING COUNT and INSERT…ON DUPLICATE KEY UPDATE

There are two main queries I'm tying to improve, one question is much more conceptual and learning about databases and another is much more straightforward. Both cases deal with the same table It has ...
0
votes
1answer
83 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 ...
2
votes
1answer
468 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 ...
1
vote
1answer
185 views

Merge duplicate rows into one row

I have a table like below in MySQL: +-----+------+-------+-----------+----------+---------+ | #id | NAME | empid | c_lan | Java_lan | Dot_lan | ...
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 ...
1
vote
1answer
74 views

How does RANK() work in this query? [closed]

I want to delete duplicate rows from a table. I have searched for the query online, and found the following sample: DELETE FROM dbo.LeadsContacts WHERE Sr_No IN ( SELECT Sr_No FROM ( ...
1
vote
1answer
1k 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 ...
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 ...
1
vote
1answer
408 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
votes
1answer
98 views

SELECT rows with duplicate values

I have a table of names in mysql InnoDB as Firstname Lastname Steve Ross John Doe J. Doe Mike Ross Michael Ross Roger ...