Hot answers tagged

15

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 ...


14

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 ...


13

I have set up a test for checking the options. I'll include the code below, which can be run in psql on a linux/Unix box (simply because for the sake of clarity in the results, I piped the output of the setup commands to /dev/null - on a Windows box one could choose a log file instead). I tried to make different results comparable by using more than one (...


12

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 ...


11

Three ways. Either IGNORE duplicate errors: 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, dated, filldate) SELECT (NULL, '...


9

Use the SQL Azure Migration Wizard: The SQL Azure Migration Wizard (SQLAzureMW) gives you the options to analyzes, generates scripts, and migrate data (via BCP) from: SQL Server to SQL Azure SQL Azure to SQL Server SQL Azure to SQL Azure


8

Concise, fast (especially with many rows), my favorite concerning readability and would work with dupes, too: SELECT count(*) = 1 AND min(val) = 1 FROM foo; Returns TRUE / FALSE .. or NULL - only in the case of exactly one row with val IS NULL, because count() never returns NULL or no row. The second 1 in the example just happens to be the same as the ...


8

In my experience (and as shown in many tests) NOT IN like demonstrated by @gsiems is rather slow, particularly with long lists. This query (doing the same, exactly) should be much faster: DELETE FROM questions_tags q WHERE EXISTS ( SELECT 1 FROM questions_tags q1 WHERE q1.ctid < q.ctid AND q.question_id = q1.question_id AND q....


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 *, ...


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 ...


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

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 ( ...


5

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 ...


5

A variation on @Erwin's answer. No COUNT() at all, only MIN() and MAX(). It may be slightly more efficient with big table and (not in your case) duplicate val: SELECT MIN(val) = 1 AND MAX(val) = 1 FROM foo;


5

First a sample table mysql> drop database if exists ali; Query OK, 1 row affected (0.10 sec) mysql> create database ali; Query OK, 1 row affected (0.00 sec) mysql> use ali; Database changed mysql> CREATE TABLE test -> ( -> id int(11) unsigned NOT NULL AUTO_INCREMENT, -> external_id int(11), -> number smallint(5), ...


5

Database duplication generally refers to restoring a physical backup of a database to a different server (preferably using RMAN). That is normally done periodically to refresh lower environments from production. Database replication generally refers to the process of copying a subset of data from one database to another on an ongoing basis. Replication ...


5

In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint. Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order. create table friends ( user_a integer not null, -- references users, not shown user_b integer not null, -...


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

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 SET Field2 = '' WHERE c > 1; Or with a more cumbersome (and potentially less efficient, did not test) self-join: UPDATE t SET Field2 = '' FROM dbo.Table1 AS ...


4

Compose a bcp script that exports the contents of all your tables to local files. Start by writing a query that will output a bcp command to export each table in your target database to a path on your destination machine: SELECT 'bcp ' + SCHEMA_NAME(schema_id) + '.' + name + ' out ' + ' D:\local_backup_directory\' + SCHEMA_NAME(...


4

Red Gate has an aptly named SQL Azure Backup Tool. It's free, but Red Gate has discontinued support for it.


4

Your options are rather limited because of your requirements to "constantly check ... sync" and "can't make any change in A". Things such as materialized view logs, dbms_alert, streams, and a standby database are all off the table. If the tables in A are constantly having all of their rows updated then (as Jack Douglas said) a materialized view would be ...


4

There is no way of knowing that a table in A has changed except by polling. You could consider Materialized Views, refreshing periodically, which can work over a dblink - but only a complete refresh is possible so this may only be practical if the tables are small.


4

You can "group by" Emp_ID and use an aggregate function like MIN() or MAX() to get one of the names: INSERT INTO TargetTable (Emp_ID, Name) SELECT Emp_ID, MIN(Name) FROM SourceTable GROUP BY Emp_ID ; And note that there is no inherent order in a table (actually you can define a clustered index for a table and this affects how the rows are stored on ...


4

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. ...


4

The tutorial you linked to is, I'm afraid, very optimistic. It forgets to note that it only covers the case of an auto generated AUTO_INCREMENT PRIMARY KEY. The methods it suggests do not cover the general case for a UNIQUE KEY, including, of course, a general PRIMARY KEY. The article does mention that "The most common problem with replication is primary ...


4

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 ...


4

The duplicate database ... from active database is designed to work without any existing backup. I would expect Oracle to clean up the archivelogs which were used during the clone process. So I would say this is an unexpected behavior. Try to log a Service Request at Oracle. I cannot recall if I had to clean up archivelogs after a clone processes. Well, I ...


4

SELECT Func(PK),count(*) FROM tab GROUP BY Func(PK) HAVING Count(*)>1 ; Where Func() is whatever you're using to clean up the PK column spaces Example: SELECT LTRIM(RTRIM(PK)),count(*) FROM tab GROUP BY LTRIM(RTRIM(PK)) HAVING Count(*)>1 ; Example2: (as suggested by Martin in the comments) WITH cte AS (SELECT ROW_NUM() OVER (PARTITION BY LTRIM(...



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