Skip to main content
Share Your Experience: Take the 2024 Developer Survey
21 votes
Accepted

How to delete duplicate records in MySQL, in a table without IDs?

There is no any field combination which identifies the record uniqually. I see at least 2 different solutions. First solution: move unique records to a copy of table and replace original table. ...
Akina's user avatar
  • 20k
18 votes

How can I display twice each stored row without using UNION ALL or a temporary table?

SELECT ta.id, ta.name FROM emp ta CROSS JOIN ( VALUES (1), (2) ) tb (id) ;
paparazzo's user avatar
  • 5,043
16 votes
Accepted

Multiple on conflict targets

Your example suggests duplicate rows in the VALUES clause itself - which would result in: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time Can be solved by folding duplicates ...
Erwin Brandstetter's user avatar
11 votes
Accepted

Duplicate rows - how to remove one?

This will work I think: with d as ( select ctid, row_number() over (partition by t.*) as rn from tablename as t ) delete from tablename as t using d where d.rn > 1 and d.ctid = t....
ypercubeᵀᴹ's user avatar
11 votes

How can I display twice each stored row without using UNION ALL or a temporary table?

One way would be SELECT COALESCE(a.id, b.id) AS id, COALESCE(a.name, b.name) AS name FROM emp a FULL OUTER JOIN emp b ON 1=0 ORDER BY id;
Martin Smith's user avatar
  • 84.8k
10 votes

How can I display twice each stored row without using UNION ALL or a temporary table?

Three more ways. Similar to Paparazzi's answer, making use that a NATURAL join becomes a CROSS join when there is no common column: SELECT e.id, e.name FROM emp AS e NATURAL JOIN (VALUES (1),...
ypercubeᵀᴹ's user avatar
10 votes
Accepted

Sum up counts in subquery

Does this do what you want? SELECT total = SUM(x.records) FROM ( SELECT records = COUNT_BIG(thecol) FROM thetable WHERE thecol IS NOT NULL GROUP ...
Erik Darling's user avatar
10 votes
Accepted

When should the IGNORE_DUP_KEY option be used on an index?

It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it. Of course, there are a couple helpful posts on the ...
Erik Darling's user avatar
9 votes
Accepted

Duplicate records returned from table with no duplicates

There are some tricky scenarios which can result in the same row being read twice from an index, even under the READ COMMITTED isolation level. Your query does not qualify for an allocation order ...
Joe Obbish's user avatar
  • 32.2k
8 votes

When should the IGNORE_DUP_KEY option be used on an index?

Every feature has a use case, and IGNORE_DUP_KEY is no exception. As I explain in IGNORE_DUP_KEY slower on clustered indexes, this option only potentially benefits performance (on a clustered index) ...
Paul White's user avatar
  • 85.5k
6 votes
Accepted

does INSERT INTO ... ON DUPLICATE KEY UPDATE executes even if the row has not changed?

MySQL executes ON DUPLICATE KEY UPDATE the same way it executes UPDATE statements: It checks the contents of each row (and columns) to be updated and if they are identical to the supplied, it does ...
ypercubeᵀᴹ's user avatar
6 votes

How do I insert record only if the record doesn't exist?

You can do this with a unique constraint & insert or ignore. Create table: ironforge:tmp phil$ sqlite3 SQLite version 3.19.3 2017-06-27 16:48:08 Enter ".help" for usage hints. Connected to a ...
Philᵀᴹ's user avatar
  • 31.8k
6 votes
Accepted

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

First off, partition by A,B,C order by A,B,C makes no sense. Since you intent to keep the "last" row (meaning the one with the greatest ID), you probably meant: partition by A,B,C order by ID Still, ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Eliminate extra rows that intersect

Then you need an < or > instead of a <>. That is similar to when you try to find dupes in your data CREATE TABLE dbo.t ( n varchar(100) NOT NULL , i geometry NOT NULL ); INSERT ...
nbk's user avatar
  • 8,424
5 votes
Accepted

Exclude All Matching Rows

Is there a way to query into the table and grab only those that have unique emails, while ignoring those that share emails? I can think of a couple ways to do this: 1) If I restate this, I might ...
AMtwo's user avatar
  • 16.2k
5 votes

Update Grouped records

You can do this much simpler using a CTE: ;WITH x AS ( SELECT Field1, Field2, c = COUNT(*) OVER(PARTITION BY Field1, Field2) FROM dbo.Table1 WHERE Field2 <> '' ) UPDATE x ...
Aaron Bertrand's user avatar
5 votes

MySql: check and remove duplicate rows

1. How can I check if a table contains duplicate rows, with the same value of the couple (date, time) ? SELECT * FROM db.table HAVING COUNT(symbol) > 1 2. How can I remove all but one row ? a) ...
Priyanka Kariya's user avatar
5 votes
Accepted

Deduplicate Data Using Having with Subquery

There are restrictions regarding self-joins in MySQL's implementation of UPDATE and DELETE. The issue is usually solvable by using joins: DELETE g.* FROM game AS g JOIN ( SELECT matchid, ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Want to clone a role, but am getting "can't login" message after doing so

The answer is simple, but may be very surprising: CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, ...
András Váczi's user avatar
4 votes

Merging two selects then sort by and remove duplicates

UNION is definitely the way to "stack" rowsets on top of each other, while removing duplicates. (Use UNION ALL to keep duplicates.) Your queries appear to have some cleanup possible, so we'll do that ...
ErikE's user avatar
  • 4,305
4 votes

Removing duplicates values with join

Use the next query SELECT B.Company, COUNT(DISTINCT A.Ticket) AS QTY FROM Table1 A INNER JOIN Table2 B ON A.CompanySk = B.EDWCompanySK WHERE A.StartedDate >= '20171128' GROUP BY B.Company ORDER BY ...
Denis Rubashkin's user avatar
4 votes
Accepted

Deleting duplicate based on latest datestamp entry

You can use a CTE to add a row number that starts at 1 from the newest datestamp for each e-mail address, then delete all the rest. ;WITH x AS ( SELECT email, origin_date, new_opt_in_date, ...
Aaron Bertrand's user avatar
4 votes
Accepted

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

We need move stocks between companies, right? What means we need add stocks to existing users and change company for new users. Or, what the same, we could delete all stocks of company "foo" ...
Melkij's user avatar
  • 3,189
4 votes
Accepted

How to deal with duplicate customers?

Identification via e-mail address typically also involves authentication by e-mail address (e.g. send a mail to the claimed address and register only upon reply to that mail). After that point (when ...
Erwin Smout's user avatar
  • 1,746
3 votes

What is effective way to delete duplicate records based on multiple columns in MySQL?

Plan A: ALTER IGNORE TABLE users ADD UNIQUE(email, alt_email, mobile and alt_mobile); This will go through the table, copy it over, ignoring any dups. I am, however, hesitant to say that it ...
Rick James's user avatar
  • 78.3k
3 votes

What is effective way to delete duplicate records based on multiple columns in MySQL?

Consider enforcing this combination of columns as a unique constraint at the database level. That won't be possible until the existing duplicates are cleaned out. But it will help prevent this problem ...
dap6000's user avatar
  • 31
3 votes
Accepted

INSERT INTO inserting multiple times

You are doing a cartesian product here: ... FROM dbo.[DataSource], @ids i you need to know which address_id corresponds to which user, and for that you must have a clause. Something like this will ...
JoseTeixeira's user avatar
  • 1,123
3 votes

Merging two selects then sort by and remove duplicates

Use the UNION keyword again. You almost had it in your second query. SELECT UM.UMKEY, OS.MAILTITLE, UM.ADDRESS01, UM.ADDRESS02, UM.ADDRESS03 FROM (OS LEFT JOIN UM ON OS.HOMEKEY = UM.UMKEY) Left Join ...
Randolph West's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible