Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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
0 votes
1 answer
32 views

Prevent creation of duplicate rows in database dynamically

I am new to databases so please forgive my mistakes. Here's the scenario. I use the web for my data source. Sometimes, like, for a keyword, what my crawler do is crawl same keyword repeatedly (not the ...
user avatar
0 votes
0 answers
20 views

Why is restoring a fresh database from a backup failing due to a duplicate key error?

I'm trying to build a replica from an active database with no downtime for replication purposes. I run the following statement to get a backup, avoid locking the tables, and flush the logs so that the ...
user avatar
0 votes
0 answers
12 views

Composing ID from connected entities' IDs?

My dynamoDB database has 2 tables which I added a many-to-many connection to. In order to prevent duplicate entries in the connection table, I want to make the its keys be in the format <...
user avatar
  • 101
0 votes
0 answers
32 views

Postgresql 14 Primary Key not working, allows duplicates

I discovered that my 3-column (integers, not null) primary key is allowing duplicates! Then I created new unique index and replace the current one, and the problem still exists, how is that even ...
user avatar
  • 109
1 vote
1 answer
118 views

UPSERT with UPDATE on variable column gives error "command cannot affect row a second time"

Hey I'm trying to insert or update(if the constraints are duplicated) based on a query result, these are the create table statement: CREATE TABLE IF NOT EXISTS public.inventory ( inventory_id serial ...
user avatar
  • 49
0 votes
1 answer
23 views

merging nearly- duplicate rows from different databases in the same table

I am sure , that many people before me faced a need to merge nearly- duplicate rows from several databases. However, I have not been able to find a simple yet general solution. I provided a data ...
user avatar
  • 11
0 votes
1 answer
51 views

Automatically Handling Duplicate Column Names (Netezza)

I am using Netezza SQL. I was able to successfully run the following query: select a.*, b.* from table_a a inner join table_b b on (a.date_1 between b.date_2 and b.date_3) and (le_dst(a.id1, b.id1) = ...
user avatar
-1 votes
2 answers
48 views

How to conditionally pick a row based on status value?

I want to select all available and bought packs. An user can open two pages to buy one pack, this results in two rows in the payment table. One gets status 'Pending' and the one that is bought gets ...
user avatar
0 votes
1 answer
33 views

Delete redudant values of resultset

Using MySQL Workbench I managed to make a create and insert script (see all they way down). With the script SELECT abbr_Company, name_Company, ddate, turnover, name_Employee FROM Profit INNER JOIN ...
user avatar
0 votes
1 answer
48 views

Concatenate duplicates within table column [duplicate]

I have a query that is returning data similar to the following: po_line.line_no (PO Line #) oe_line_po.order_number (Sales order #) 1 A 1 B 1 C 2 D 3 E 3 F 4 null Some of our po_line.line_no ...
user avatar
0 votes
1 answer
152 views

How to eliminate duplicates made by recursive CTE [closed]

I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges): CREATE TABLE tmp_deps ( id bigint, code text, name text, start_date date, ...
user avatar
  • 123
0 votes
2 answers
1k views

Avoid duplicates in PostgreSQL among the rows that are going to be inserted

I have a destination table a and a set of source tables b and c. I'm trying to do an insert to a from b and c, and additionally check if such combination of (column1, column2) do not already exists in ...
user avatar
3 votes
2 answers
1k views

How to handle duplicates in an UPDATE on a primary key in Postgres?

Let's assume a scenario with the entities Person and Company and a table PersonCompanyStocks that models how many stocks a persons owns of a certain company (N:M cardinality). For example: person | ...
user avatar
4 votes
1 answer
65 views

Eliminate extra rows that intersect

I have a table of geometry values where some rows intersect other rows. I need a list of rows where the geometry overlaps other rows, but I'd like the list to be as concise as possible. Here's the ...
user avatar
  • 67.1k
0 votes
2 answers
501 views

SELECT DISTINCT in extra space data from a single column ( no duplicates )

Hello i am having problems with this column data cities Charlotte Cincinnati Cleves Columbus Fairfield Fresno Los Angeles -- HERE IS THE PROBLEM Los Angeles Madison Manhatttan Beach New York Orange ...
user avatar
0 votes
1 answer
83 views

Oracle 12cR1 - Duplicate Database With No Resetlogs

Database version: Oracle 12cR1 Operating system: Oracle Linux 7.x ASM: yes Duplicate script: cat duplicatetodisaster.sh ORACLE_UNQNAME=xxx ORACLE_SID=xxx ORACLE_HOME=xxx rman target sys/***@PROD ...
user avatar
0 votes
1 answer
105 views

Forced Parameterization generating duplicate plans

https://www.brentozar.com/pastetheplan/?id=H1qoS7BSu I am using Azure SQL 18 vCore db with Forced Parameterization, and MAXDOP 8. The vCore count may change much lower for other dbs but for the same ...
user avatar
2 votes
1 answer
53 views

How to code something like 'Update on duplicate key update to something else'?

As part of dealing with the UK's GDPA 'right to be forgotten' in a small database I want to update the (autoinc) PK of a row in table 'member' to an obfuscated value and let it cascade down through ...
user avatar
0 votes
0 answers
13 views

how to Manipulate duplicated records using access

Using Microsoft Access, I have a number of records that have duplicated field-1. I need to form a query such that the duplicate records get unique ; BUT the next filed-2 data must be seen in columns ...
user avatar
1 vote
3 answers
62 views

Can I improve my duplicate-detection queries?

I have a DB table with US domestic flight data which I am in the process of sorta-kinda-de-duplicating. The schema appears here, but it is probably not very important. There are no perfectly-duplicate ...
user avatar
  • 113
0 votes
1 answer
46 views

Postgresql Union: Do not repeat based on 1 column only

UNION filters out duplicate entries while UNION ALL keeps duplicate. Similar to that scenario, Consider this anological case, I want to set union to check only single column duplicacy. SELECT id, 1 AS ...
user avatar
  • 209
2 votes
1 answer
416 views

MySQL table with identical name multiple times in same database

I stumbled into a pretty nasty bug into our PROD environment I was running some alter scripts and it appears it ended up duplicating the table for each column altered. I've tested with MySQL Workbench,...
user avatar
  • 121
0 votes
1 answer
23 views

need help building a query to detect duplicates on multiple columns

I have the following table create table catalog_product_entity_varchar ( value_id int auto_increment comment 'Value ID' primary key, attribute_id smallint unsigned default 0 not ...
user avatar
  • 147
0 votes
1 answer
24 views

Delete every row that is not the first duplicate

I want to enforce uniqueness on a column, but in order to do so, I need to manually clean up a lot of duplicates. I have found out that all the duplicates have no references to them, so a simple ...
user avatar
  • 153
1 vote
1 answer
38 views

Why can't I insert an email on a table that has one more dot than an existing one? [closed]

I'm using the official Heroku add-on for PostgreSQL and I have a database running on version 12.4 of PostgreSQL. I'm facing an issue where on my users table I have an email like this fakeemail@domain....
user avatar
1 vote
0 answers
79 views

Preventing three SQL Left Joins from creating duplicate rows

I have 4 tables [tA] is the main data point. A customer could have more than one ID Code, or the same ID Code which has been renewed, and ID Codes can be transferred between customers, but a customer ...
user avatar
0 votes
2 answers
307 views

In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?

In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row). How can ...
user avatar
  • 103
-1 votes
1 answer
3k views

Error 1062 duplicate entry key 1 for key primary [closed]

I have a problem when I try to import data create table song ( artist_id int not null, album_id int not null, song_name varchar(75), tracknum int not null, primary key (artist_id, ...
user avatar
0 votes
1 answer
56 views

Remove duplicate records with special rules - Is it possible with a single query?

I have the following data in a table: id loc1 loc2 zip_code ------------------------------ 10 1 null 12345 10 null 1 null 11 1 null null 11 null 1 ...
user avatar
  • 131
0 votes
1 answer
15 views

possible to INclude duplicate rows?

I have MariaDB version 10.2.14-MariaDB. I have a table that looks like this: CREATE TABLE `card_data_link` ( `card_id` INT(11) NULL DEFAULT NULL, `data_id` INT(11) NULL DEFAULT NULL, INDEX ...
user avatar
  • 234
0 votes
2 answers
30 views

Insert Only first Occurence in Oracle table

i have a table CREATE TABLE PEOPLE ( NAME VARCHAR(64 BYTE)PRIMARY KEY NOT NULL, TIME TIMESTAMP(6), NICKNAME VARCHAR2(16 BYTE), STATE VARCHAR2(50 BYTE), CITY VARCHAR(128 BYTE) ) I want to insert in ...
user avatar
1 vote
0 answers
37 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 ...
user avatar
  • 131
0 votes
0 answers
448 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 ...
user avatar
-2 votes
1 answer
35 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 | +----+------+-----------...
user avatar
0 votes
1 answer
25 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 ...
user avatar
1 vote
1 answer
406 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 ...
user avatar
  • 157
0 votes
2 answers
794 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... | +----+--------------+ ...
user avatar
0 votes
1 answer
41 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 ...
user avatar
0 votes
1 answer
34 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) ...
user avatar
  • 101
-1 votes
1 answer
227 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 ...
user avatar
  • 153
0 votes
1 answer
609 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 ...
user avatar
0 votes
1 answer
1k 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 ...
user avatar
  • 3
0 votes
2 answers
749 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 ...
user avatar
1 vote
2 answers
69 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,...
user avatar
0 votes
1 answer
64 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, ...
user avatar
  • 21
1 vote
1 answer
33 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 ...
user avatar
  • 125
0 votes
1 answer
73 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 ...
user avatar
0 votes
1 answer
10k 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 ...
user avatar
7 votes
3 answers
2k 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 ...
user avatar
  • 505
1 vote
0 answers
25 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 ...
user avatar

1
2 3 4 5