Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

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
-2 votes
1 answer
55 views

How to remove violation of duplicate primary key in the table in sql server? [closed]

I am encountering a customer error as they are unable to print due to violation of primary key as being duplicate, below its where i went and check for the db column in the table and saw this as the ...
thrue2023's user avatar
3 votes
1 answer
226 views

Finding all the index corruptions in the entire Postgres DB

There is DB with unknown amount of corrupted unique indexes. Currently I discover them one by one when I try REINDEX DATABASE CONCURRENTLY <db_name>, handle the specific duplication violation (...
KaliTheGreat's user avatar
1 vote
1 answer
88 views

Query to find when a specific current value changed

I have 3 tables of data (all contain more columns, but these are the ones I need from each): t1: table1 workID1 h1Amt h1Dt status 101 73.00 12/1/2023 A 124 0.00 10/30/2023 D 251 0.00 3/1/2023 A ...
tkmagnet's user avatar
0 votes
1 answer
55 views

Duplicating from active database fails at connection

I'm trying to duplicate a database from active database, but it seems like my connection is blocked on my auxiliary. I'm using ASM just4info. Target DB: listener.ora: LISTENER = (DESCRIPTION_LIST = ...
paganini's user avatar
  • 118
0 votes
1 answer
56 views

History Logging Trigger Causing Duplication

I'm fairly certain I'm probably just overlooking something simple but here's the situation: I'm trying to create a "reusable" historical logging trigger for a production database. The intent ...
G_Hosa_Phat's user avatar
0 votes
1 answer
19 views

Flag identical group of rows

I would need to find a way to identify lines that are the same. The comparison must be made on the first 4 columns (A-B-C-D) If these are identical I would like to identify them by inserting 1 in ...
Olivier's user avatar
0 votes
1 answer
74 views

What is the best option to remove duplicates in big heap?

Our Server is SQL Server 2016 Database with Full Recovery model. We have table "A" - heap which has 280 millions of records and size 90 GB with 50 columns. This table contains around 5 ...
adam.g's user avatar
  • 355
0 votes
1 answer
44 views

Quickly duplicate the same row with 1 random column

I need assistance duplicating a single row in phpmyadmin 1000 times with the same data except for 1 column for codes. The code has to be random. Here's a sample: Is there an easier way to do this ...
iJchael's user avatar
2 votes
1 answer
532 views

DISTINCT gives duplicate values

Can it be considered a bug? This query gives duplicate values despite DISTINCT: select distinct '1' from ( select * from dual connect by level <= 10 ) order by dbms_random.value fetch first 10 ...
Andy DB Analyst's user avatar
0 votes
0 answers
24 views

Representing state of duplicate records across databases

I'm needing help formalizing this problem so that I can better get a handle on it. Or so I can at least better do google searches for it. Suppose an external system and your system maintain the same ...
Orion Yeung's user avatar
1 vote
1 answer
439 views

Query optimisation - how to reference lateral join table to avoid duplicate lookup

First time posting here but long time reader. I'm fairly new in my role working on optimising sql queries. Most are generated from KNEX.js leading to some peculiar artifacts that might make sense in ...
George B's user avatar
1 vote
1 answer
327 views

Find duplicate values by joining tables SQL server

Finding the duplicate values in the 'Item_Sales_Detail' table as NULL rows in the 'Sales' and 'Item' tables by joining three tables. 'Sales' table (ID is primary key) ID Invoice Date TotalAmount 10 ...
Mohammad Bastan's user avatar
0 votes
1 answer
354 views

PostgreSQL duplicate non-null values despite unique constraint

I have a database with a unique constraint in a field that is apparently being ignored. That table also includes two rows with the same value for that field, but somehow querying by that value only ...
Alex's user avatar
  • 3
3 votes
3 answers
801 views

Is it ok to have repeated values in some of the columns and similar columns in two separate tables?

I'm trying to create database in which I could store thousands of job postings. I put here 3 sample records of these job postings so that you could better understand how everything looks like: Every ...
beginsql's user avatar
0 votes
0 answers
98 views

How to prevent insertion of row which differs only in primary key from existing row?

If my table contains the following +----+-----+------------+-----------+ | id | uid | date | operation | +----+-----+------------+-----------+ | 1 | abc | 2022-09-21 | create | | 2 | xyz | ...
loris's user avatar
  • 103
1 vote
1 answer
140 views

How to deal with duplicate customers?

This is probably common knowledge for DBAs, but in my database I am struggling with a simple registration of customers. Customers can sign up using a form (we use typeform), meaning we create customer ...
Jeroen Vermunt's user avatar
2 votes
0 answers
199 views

My relational schema and a few questions about relations and duplicate values in columns

I'm trying to create database in which I could store thousands of job postings. I put here 5 sample records of these job postings so that you could better understand how everything looks like: Now, ...
beginsql's user avatar
2 votes
2 answers
524 views

How to remove only adjacent duplicates in an array?

While aggregating an array, I need to remove empty strings and then combine all adjacent identical values. E.g.: ["","product","product","","product",&...
AJ AJ's user avatar
  • 125
0 votes
1 answer
27 views

TVGuide database recurring TV Show

I am trying to design TVGuide database as you can see one TV program can have many shows, as well as one show can play on multiple tv programes this is all going fine The problem I am trying to solve ...
LimetaPeta's user avatar
0 votes
1 answer
68 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 ...
Aman Rawat's user avatar
0 votes
0 answers
437 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 ...
Timothy Fisher's user avatar
2 votes
0 answers
846 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 ...
sh4rkyy's user avatar
  • 139
1 vote
1 answer
1k 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 ...
IBot's user avatar
  • 149
0 votes
1 answer
174 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 ...
YW T's user avatar
  • 11
-1 votes
2 answers
285 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 ...
KarelDeGrote's user avatar
0 votes
1 answer
36 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 ...
user2494430's user avatar
0 votes
1 answer
662 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 ...
sullivan.syd's user avatar
0 votes
1 answer
1k 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, ...
Dmitry K.'s user avatar
  • 143
1 vote
2 answers
8k 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 ...
Radu Dumbrăveanu's user avatar
3 votes
2 answers
6k 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 | ...
bluenote10's user avatar
4 votes
1 answer
83 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 ...
Hannah Vernon's user avatar
  • 70.1k
1 vote
2 answers
2k 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 ...
rubengavidia0x's user avatar
0 votes
1 answer
358 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 ...
jrdba123's user avatar
0 votes
1 answer
263 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 ...
robertys's user avatar
2 votes
1 answer
208 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 ...
user2834566's user avatar
0 votes
0 answers
22 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 ...
user4549384's user avatar
1 vote
3 answers
241 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 ...
einpoklum's user avatar
  • 113
0 votes
1 answer
2k 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 ...
PaxPrz's user avatar
  • 219
2 votes
1 answer
774 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,...
Fiur's user avatar
  • 121
0 votes
1 answer
40 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 ...
gabtzi's user avatar
  • 161
0 votes
1 answer
30 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 ...
oligofren's user avatar
  • 155
1 vote
1 answer
212 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....
João Pinto's user avatar
1 vote
0 answers
212 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 ...
sql-newbie's user avatar
0 votes
2 answers
915 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 ...
relima's user avatar
  • 103
-1 votes
1 answer
15k 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, ...
Caylan Kowalski's user avatar
0 votes
1 answer
166 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 ...
advncd's user avatar
  • 131
0 votes
1 answer
19 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 ...
raphael75's user avatar
  • 244
0 votes
2 answers
113 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 ...
Dallincha's user avatar
1 vote
0 answers
102 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 ...
Balmipour's user avatar
  • 131
0 votes
0 answers
914 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 ...
Serge Rogatch's user avatar

1
2 3 4 5 6