Questions tagged [duplication]

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.

Filter by
Sorted by
Tagged with
1
vote
0answers
18 views

Composed primary keys : “should” duplicates and null values be accepted, and according to which norm?

I just passed some SQL test including a question about the possibility to have duplicates in a primary key column. The expected answer was NO. Mine is, "Yes, if the column is part of a composed ...
0
votes
0answers
17 views

Bulk INSERT … ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB

I have a table like the following: CREATE TABLE `demo_upsert` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci', `LastListID` BIGINT(20) NULL ...
-2
votes
1answer
33 views

If all associated attributes of an intended primary key are identical, is it still a true primary key?

I'll start with an example. If I have a person table with intended surrogate primary key Id: +----+------+------------+-------------+ | Id | name | DoB | SSN | +----+------+-----------...
0
votes
1answer
16 views

Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table

Looking at 3 Tables - Recipient, HouseholdScores, Sales. I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date ...
1
vote
1answer
29 views

Duplicating rows based on a count column SQL Server [duplicate]

I have the following table: MyTable PlaceID NameID Code CodeCount 1 1 1234A 2 1 2 1234A 3 1 3 1234A 1 2... 2... 3... 3... I want to select 1234A where ...
0
votes
2answers
43 views

mysql - How to remove duplicate rows in parent table and leave one row with the lowest ID, and replace foreign key in child's table with this ID?

I have the situation where I have three tables: hgn_modern_sources +----+--------------+ | id | source | +----+--------------+ | 17 | Something... | +----+--------------+ ...
0
votes
1answer
32 views

Is it “bad design” to have 2 related tables, each one having a column (named differently) whose value will always be the same in both?

Imagine that I have the following database structure TableUser ( userId integer, weeksToLive integer ) TableCalendar ( calendarId integer, numberOfCells integer, userId integer ...
0
votes
1answer
26 views

Remove “almost” duplicate rows (may or may not start with plus sign)

To remove duplicated rows from Oracle, I tend to use the following: DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2, column3); (taken from SO) ...
-1
votes
1answer
55 views

Displaying only 1 row for each distinct value

This seems to be a problem I have quite frequently, but I have yet figure out (I'm not a dba). I'm running a query for a report I'm creating, however the user does not want any duplicate rows ...
0
votes
1answer
85 views

SQLite - Remove duplicates across multiple columns

I have the following table: C1 | C2 | C3 ------------- A | X | 1 A | Y | 2 B | X | 3 B | Y | 4 I want to deduplicate across columns 1 and 2, and select the max from column 3. In case, since rows 1 ...
0
votes
1answer
211 views

Mysql output to csv putting column names on the bottom

I'm trying to export the results of the query listed below to csv and have the column names in the first row. However, when I run the code and open up the csv the header is listed as the last row. I ...
0
votes
2answers
41 views

Locate multiple duplicate columns in PostgreSQL table

I am trying to report on duplicate records in a single table which has a unique key of app_cao_number. The duplicates occur if either: 1. The Passport field is duplicated; 2. The ID field is ...
1
vote
2answers
50 views

Find rows in a single table where values in a certain column are near-duplicates

I have a Postgres 11 table on RDS containing a column email; some of the values in this column [and only that column] are clearly de facto duplicates but differ in case, i.e., different capitalization,...
0
votes
1answer
47 views

How to remove these duplicate records in an Oracle Database 12c?

I have these two tables: messages(id primary key, message_date, created_at, ...) user_messages(user_id, message_id references messages(id)) I have some duplicate rows in messages: select user_id, ...
1
vote
1answer
18 views

how to approach user acceptance testing for merged constituent records in a CRM system?

I've been Googling to read up and understand what UATs are and how to conduct them, but I'm not quite understanding how to apply it in this scenario. My acceptance criteria would be that all ...
0
votes
1answer
30 views

SQL - Finding Duplicates Across Columns

I have a table that has 4 e-mail fields. A single contact can have anywhere from 1-4 e-mail addresses on file. I'm having trouble writing a query that displays all duplicates across columns. For my ...
0
votes
1answer
2k views

There is a column named in table but it cannot be referenced from this part of the query

I've been learning Postgres (coming from SQL Server), and this error really confuses me. Here is the code with some sample data: create table T ( ID serial primary key, A varchar(1), B varchar(1), C ...
3
votes
2answers
454 views

When should the IGNORE_DUP_KEY option be used on an index?

Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index. My goal is to ...
1
vote
0answers
21 views

Selecting Duplicates on All Fields

I have an MS Access (no laughing at the back) database I've used to import a bunch of IIS logs into. Having looked at the Excel files I pulled these in from, I'm worried going by the dates that some ...
1
vote
1answer
78 views

Duplicate rows with primary key constraint in PostgreSQL 10.6

I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup. However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table ...
0
votes
1answer
46 views

Deleting duplicate based on latest datestamp entry

I have a table and I'm trying to remove all the duplicate and keep the the rows that has the latest datestamp. Here is the table: email address orgin_date new_opt_in_date datestamp 123@...
0
votes
0answers
58 views

How to properly find duplicate rows with spatial and temporal criteria

I have a table with historical rows indicating the positions in which ocean samples were taken. From this table, I would like to find duplicates based on spatial and temporal criteria. Thus, the ...
0
votes
1answer
939 views

RMAN Duplication - Oracle Data Guard - Syntax error(s) RMAN-01009 / RMAN-01008 / RMAN-01007

New to DB. Interested in Oracle Data Guard. New working with RMAN. All of this is for testing only and does not involve any kind of production env. Making use of ORACLE documentation: Step by Step ...
-1
votes
2answers
30 views

SQL to return preferred duplicate rows

Have a data set that some rows have a duplicate. Looking to return the row with IND = Y if it is present, otherwise to return the N. +------------+---------------+------------+----------+ | ...
2
votes
2answers
3k views

Sum up counts in subquery

I am writing a query to check if I have got duplicates in a certain column. SELECT COUNT(thecol) FROM thetable WHERE thecol IS NOT NULL GROUP BY thecol HAVING COUNT(*) > 1 That will give me an ...
1
vote
2answers
46 views

Comparing duplicates and choosing the duplicate with most recent date in a join

Assume i have table A: id: name: timestamp: 1 Bob 2018-05-31 04:46:37 2 Alice 2019-01-31 07:13:56 1 Bob 2019-01-15 18:23:74 And i want to join with another table B: ...
1
vote
2answers
86 views

How to remove partial duplicates from a table

I have a database that contains some erroneous results that are essentially but not technically duplicates. The structure is like this: id_page (PK), id_site, label, create_date There should only be ...
0
votes
1answer
30 views

Removing duplicate-ish paths generated from recursive CTE

I have some multi-parented data, that I run a recursive CTE against to find common ancestors: item_id |item_name |path_name |id_path_array | --------|----------|-------------|--------------| 1 ...
0
votes
1answer
3k views

How Compare two columns of Same SQL table to find duplicate records

I have one table with two columns: StartDateTime EndDateTime What will be best query to find out which rows has same (StartDateTime, EndDateTime)?
0
votes
1answer
26 views

Looking for a query that eliminates near-repeats

I have a database with four rows: county_a, county_b, flow_a_to_b, and flow_b_to_a. Essentially, the data is repeating in other rows, but the values are switched around. Here's an example: Entry 1: ...
1
vote
0answers
121 views

MS SQL, fastest way to delete 99% of rows from 100+ million rows table to deduplicate data

Due to an error in our application, one of the tables in our MS SQL has been flooded with duplicate rows differing only in PK identity column and timestamp +--------+---------------------+------------...
0
votes
1answer
4k views

Removing duplicates values with join

I have a query that looks like this: SELECT B.Company, COUNT(*) AS QTY FROM Table1 A INNER JOIN Table2 B ON A.CompanySk = B.EDWCompanySK WHERE A.StartedDate >= '20171128' GROUP BY B.Company ORDER ...
0
votes
2answers
2k views

Select from multiple rows without duplicate values, with all random data

I have a table that gets results from three different sources. Each column represents a source, and each row a result of an outcome. There are over 50k rows for a total of 150k results. I need to ...
2
votes
2answers
82 views

Ignore duplicates while using join

I'm trying to ignore duplicates between two providers in my tables while using join. I'm using this query for now: SELECT * FROM A AS a1, A AS a2 WHERE a1.provider <> a2.provider AND a1.etype =...
2
votes
2answers
402 views

Duplicate index - Same key, one with INCLUDE columns, which one to drop?

I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop? They are the same, except IndexInclude has 6 INCLUDE columns. Both have some reads (IndexNoInclude ...
-1
votes
1answer
33 views

eliminating duplicates

I want to pull users that only have ONE deviceID in a given month. Let's say I have the following table: User Device ID Greg 1 Greg 2 Mike 3 Laura 4 Laura 5 Lisa 6 George 7 Peter ...
1
vote
1answer
64 views

Delete duplicates and reroute referencing rows to new master

Given three tables: A users table with id's: ╔════╗ ║ id ║ ╠════╣ ║ 1 ║ ╟────╢ ║ 2 ║ ╟────╢ ║ 3 ║ ╚════╝ A purposes table with user_id's and name's: ╔════╤═════════╤══════════╗ ║ id │ user_id │ ...
0
votes
2answers
395 views

How to delete records without a primary key in a stardard way (i.e. not using ctid)

Case of use We have a log table without a PK with two columns (date, event); You inserted a row. Then you want to undo the inserted record. Is there a way to delete that row (without deleting ...
2
votes
2answers
6k views

Insert into Table ignoring duplicate values

I am having a brain fart on figuring this out. I have the following two Tables: Table: parts part_id INT IDENTITY(1,1) NOT NULL, part_number VARCHAR(50) UNIQUE NOT NULL, ...
0
votes
1answer
2k views

oracle sql: How to not select select duplicate records from table

Can any help me to fix the below sql query SELECT DISTINCT ORDER_NUMBER, FLAG FROM TABLE Below was the result Order_number FLAG LP-13288 false LP-13288 true LP-13292 false LP-13290 ...
2
votes
3answers
379 views

Exclude Duplicates With Certain Value

I'm been beating my head up against a wall with this all day. I have a SQL Server table called Accounts CREATE TABLE [dbo].[Accounts]( [IDNum] [INT] NULL, [FirstName] [VARCHAR](50) NULL, ...
3
votes
1answer
1k views

How to remove duplicates from any array and preserve ordering in PostgreSQL?

I'm looking for a way to eliminate duplicates from a PostgreSQL array while preserving the ordering of its elements. What I currently have are the following functions: create function array_unique( ...
2
votes
1answer
35 views

Looking for help reporting duplicates and null marks

Someone over at StackOverflow suggested this might be the place to ask this. I hope this will question is proper for this forum. I am looking for suggestions / criticisms of alternative methods of ...
5
votes
1answer
1k views

Transfer row from 'parent' to 'child' without creating duplicate and without knowing all 'parent' columns

I want to set up a database with different schemas that correspond to different data-entry systems. There needs to be a “master” (administration) schema with a “parties” table, and multiple other ...
1
vote
1answer
107 views

MySQL - How to convert an existing int column with duplicates to auto-increment?

After adding some thousand records in a table, I noticed that the "Primary" and "Auto-Increment" properties of the main column are removed. Now instead of auto-incremented numbers, most of them (but ...
1
vote
0answers
56 views

How to remove duplicate records from result set of owner

I'm working with one project like rental agreement system. There are two roles types of users (User Role) Signer = 0 Observer = 1 Anyone can register and create a document(which will be referred as ...
10
votes
1answer
6k views

Multiple on conflict targets

I have two unique indexes on columns a and b. I need something like this: insert into my_table (a, b) values (1, 2), (1, 2) on conflict (a) do update set c = 'a_violation' on conflict (b) do update ...
1
vote
1answer
28 views

Oracle 11gR2, duplicating and refreshing cloned database

Assume I've cloned PROD database to TEST with RMAN "duplicate target database to CLONE from active database;". After several days I need to refresh CLONE. Will RMAN transfer all the datafiles from ...
0
votes
1answer
821 views

How to find index details on Temporary tables in SYBASE

I am a layman in SYBASE world. In order to improve performance, I am creating indexes after data load of temporary tables in many stored procedures. Say, one proc A has an insert into temporary ...
1
vote
2answers
78 views

How can I do a query that shows any row that have more than twice in the same table without using GROUP BY or COUNT

I want to select all same item that occurs in the same table twice without GROUP BY or COUNT sentence. How to write the query? For example, if the table is: City --------- New York Los Angeles ...

1
2 3 4 5