41

HAVING is a great aggregate filter. (http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html) For example, select the article_titles with more than on occurrence: SELECT count(*), article_title FROM articles GROUP BY article_title HAVING COUNT(*) > 1; Adding columns to the SELECT and GROUP BY clauses allow you to locate duplicates based on a ...


36

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data): (TABLE a EXCEPT TABLE b) UNION ALL (TABLE b EXCEPT TABLE a) ; Or with EXISTS to return just a boolean value or a string with one of the 2 possible ...


28

One option is to use a FULL OUTER JOIN between the two tables in the following form: SELECT count (1) FROM table_a a FULL OUTER JOIN table_b b USING (<list of columns to compare>) WHERE a.id IS NULL OR b.id IS NULL ; For example: CREATE TABLE a (id int, val text); INSERT INTO a VALUES (1, 'foo'), (2, 'bar'); CREATE ...


24

Three ways. Either IGNORE duplicate errors (but don't use that, see note in the end): INSERT IGNORE ... ; -- without ON DUPLICATE KEY or try to do a redundant update when there is a duplicate: INSERT ... ON DUPLICATE KEY UPDATE id = id ; or check for duplicates before inserting: INSERT INTO requests (id, ctg, msg, nick, filled, ...


16

SELECT ta.id, ta.name FROM emp ta CROSS JOIN ( VALUES (1), (2) ) tb (id) ;


15

In my experience (and as shown in many tests) NOT IN as demonstrated by @gsiems is rather slow and scales terribly. The inverse IN is typically faster (where you can reformulate that way, like in this case), but this query with EXISTS (doing exactly what you asked) should be much faster yet - with big tables by orders of magnitude: DELETE FROM questions_tags ...


13

If you just want to disambiguate two rows with similar content, you can use the ROWID functionality in SQLite3, which helps uniquely identify each row in the table. Something like this: DELETE FROM sms WHERE rowid NOT IN (SELECT min(rowid) FROM sms GROUP BY address, body); should work to get you the rows with the minimum rowid, which will be the first ...


13

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. CREATE TABLE temp LIKE products; INSERT INTO temp SELECT DISTINCT * FROM products; DROP TABLE products; RENAME TABLE temp TO products; Second solution: add ...


10

Your problem can be solved with this query: SELECT * FROM article WHERE article_title IN (SELECT * FROM (SELECT article_title FROM article GROUP BY article_title HAVING COUNT(article_title) > 1) AS a);


10

You can use a CTE for this, if you want the row that's returned to be a complete, intact row rather than aggregates of any of the other columns. You can change the ORDER BY to prefer rows by any of the columns (the grouping is by the ones you think should be unique). ;WITH x AS ( SELECT col1, col2, col3, rn = ROW_NUMBER() OVER ( ...


10

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;


10

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 in the input. See: How to include excluded rows in RETURNING from INSERT … ON CONFLICT But a related problem affects your outlined query as a whole, and that'...


9

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.ctid ; And another variation. Not sure which will be more efficient: delete from tablename as t where exists ( select * from tablename as d ...


9

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), (2)) AS c (i) ; Another that uses UNION DISTINCT and an extra column to avoid the removal of duplicates: SELECT id, name FROM ( SELECT id, name, 1 AS d ...


9

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 scan, so the storage engine will read the data from the table in the order of the clustered key. For your table, you have the InProgress as the first column of ...


9

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 subject by Paul White: IGNORE_DUP_KEY slower on clustered indexes A creative use of IGNORE_DUP_KEY If your concern is around upsert patterns (or something similar),...


8

By using the ROW_NUMBER() ranking function, you could achieve that. Here's an example below. The top two queries are just for data verification. I'm a firm believer that you should see what you will be deleting (and what will remain) before actually deleting data. (Verification) Rows that will be deleted ;with cte as ( select *, ...


8

Well this is the insert bit that you are using: INSERT INTO example (a, b, c) VALUES (1,2,3) .... here you are not specifying the id (the primary key to be checked for duplication). Since, it is set to auto-increment, it automatically sets the id for the next row with just the values for columns a, b and c. The row in this case is updated when you ...


8

Your Original Query INSERT INTO example (a, b, c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c); If you consider (a,b,c) a unique key, there are two things you need to do First, add a unique index ALTER TABLE example ADD UNIQUE KEY abc_ndx (a,b,c); so the table structure would become CREATE TABLE IF NOT EXISTS `...


7

This looks like you might have hit a bug logged against Percona Server 5.5: Concurrent duplicate inserts can violate a unique key constraint in InnoDB tables. There is no fix and no reproducible test case for this bug yet. It has only been observed in a production environment. The pattern described is: INSERT a value into a column with a unique constraint....


7

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) when the number of duplicate key exceptions is sufficiently small. The break-even point is system-dependant, and requires realistic testing to properly ...


6

You can use the ctid to accomplish that. For example: Create a table with duplicates: =# create table foo (id1 integer, id2 integer); CREATE TABLE =# insert into foo values (1,1), (1, 2), (1, 2), (1, 3); INSERT 0 4 =# select * from foo; id1 | id2 -----+----- 1 | 1 1 | 2 1 | 2 1 | 3 (4 rows) Select the duplicate data: =# select foo....


6

Sounds like a simple cross join: select a.id, b.id from input_table a cross join input_table b where a.id <> b.id;


6

As already mentioned by the other answer, IGNORE_DUP_KEY is not supported for filtered indexes, which is explicitly stated in the manual: Filtered indexes do not allow the IGNORE_DUP_KEY option. There is a workaround, however, if you can afford a small change to the table's structure. With the help of a uniquifier column you can create a unique index ...


6

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 not do any update. It still has to check them though. So the result in your case (where you send 3 rows to be inserted) would be: 3 rows to be inserted 3 key ...


6

This can indeed be done using pure SQL: create function array_unique_stable(p_input anyarray) returns anyarray immutable strict parallel safe language sql as $$ select array_agg(t order by x) from ( select distinct on (t) t,x from unnest(p_input) with ordinality as p(t,x) order by t,x ) t2; $$ The unnest(p_input) with ordinality will return the ...


5

if you want to change the value with the new one, you can use: ON DUPLICATE KEY UPDATE doctets = VALUES(doctets) ; or if you want to add the new value to the existing one: ON DUPLICATE KEY UPDATE doctets = doctets + VALUES(doctets) ;


5

You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them. One solution is to individually GROUP BY and aggregate first (one aggregation for the list ...


5

If my source database uses SPFILE then do I have create a PFILE from the SPFILE? Yes, you need to create a temporary PFILE to use while duplicating the database. You will later switch the new instance to use the SPFILE. Use CREATE PFILE = 'path/to/pfile' FROM SPFILE; You only need to create directories that are referenced in the PFILE or SPFILE. ...


5

The syntax is just slightly different: DELETE q FROM dbo.questions_tags AS q WHERE EXISTS ( SELECT 1 FROM dbo.questions_tags AS q1 WHERE q1.ctid < q.ctid AND q.question_id = q1.question_id AND q.tag_id = q1.tag_id ); Personally, I prefer to use a CTE. Then I can easily swap in a SELECT to validate what I am about to delete, and ...


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