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.
253
questions
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 ...
- 1
0
votes
1
answer
52
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 ...
- 295
0
votes
1
answer
33
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 ...
- 3
2
votes
1
answer
105
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 ...
0
votes
0
answers
23
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 ...
- 101
0
votes
0
answers
34
views
Get value of column B by having column A duplicate IDs
I have table of policies and i need to get policies that do not have "Policy" status.
Every policy have the same POLICY_DETAILS_ID but different POLICY_STATUS_DEBUG.
The query should say: ...
1
vote
1
answer
264
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 ...
- 13
1
vote
1
answer
222
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
...
0
votes
1
answer
282
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 ...
- 3
2
votes
3
answers
463
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 ...
- 55
0
votes
0
answers
86
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 | ...
- 103
1
vote
1
answer
120
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 ...
- 113
2
votes
0
answers
163
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, ...
- 55
2
votes
2
answers
427
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",&...
- 25
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 ...
0
votes
1
answer
61
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 ...
0
votes
0
answers
386
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 ...
- 167
2
votes
0
answers
739
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 ...
- 129
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 ...
- 149
0
votes
1
answer
157
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 ...
- 11
-1
votes
2
answers
247
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 ...
- 11
0
votes
1
answer
35
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 ...
0
votes
1
answer
542
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 ...
- 11
0
votes
1
answer
988
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,
...
- 143
1
vote
2
answers
7k
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 ...
- 397
3
votes
2
answers
5k
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 | ...
- 172
4
votes
1
answer
80
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 ...
- 69.7k
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
...
- 135
0
votes
1
answer
322
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 ...
- 29
0
votes
1
answer
229
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 ...
- 3
2
votes
1
answer
180
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 ...
- 209
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 ...
1
vote
3
answers
204
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 ...
- 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 ...
- 219
2
votes
1
answer
723
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,...
- 121
0
votes
1
answer
37
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 ...
- 149
0
votes
1
answer
29
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 ...
- 155
1
vote
1
answer
197
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....
- 181
1
vote
0
answers
178
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 ...
- 11
0
votes
2
answers
848
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 ...
- 103
-1
votes
1
answer
14k
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, ...
0
votes
1
answer
156
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 ...
- 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 ...
- 244
0
votes
2
answers
101
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 ...
- 15
1
vote
0
answers
91
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 ...
- 131
0
votes
0
answers
897
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 ...
- 115
-2
votes
1
answer
38
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
1
answer
29
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 ...
2
votes
1
answer
1k
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 ...
- 167
0
votes
2
answers
2k
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... |
+----+--------------+
...
- 25