Hot answers tagged

12

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

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


11

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


10

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


6

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


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

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


4

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


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\' + ...


4

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


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

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

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


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

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


3

One way to do this (check the SQLfiddle): select p1.id as id1, p2.id as id2 from people p1 join people p2 on p1.first_name = p2.first_name and p1.last_name = p2.last_name and p1.id < p2.id where not exists ( select 1 from ( select * from attributes a1 where a1.person_id = p1.id union all ...


3

--======================================================= -- delete the duplicate records from table @t -- keeping a single unit of each -- marcelo miorelli 24-nov-2014 --======================================================= --======================================================= --create a table variable and insert records in it -- just for this ...


3

Supposing you have no foreign keys referencing that table, you could do something like create table some_table as select min(id), name, age, x from t group by name, age, x then you can drop the old table, rename the new table so that it has the same name as the old one before, and create indexes and other things you need on that table.


3

if you want to select unique record from other join table which has same FK in multiple row you can use your query as SELECT wpss_quesset.quesset, wpss_questions.* FROM wpss_quesset JOIN wpss_questions ON wpss_quesset.id = wpss_questions.ques_set_id GROUP BY wpss_quesset.id OR If you want to get all record that of ...


3

Assuming the following table definition CREATE TABLE T ( Name VARCHAR(50) NOT NULL, IsDeleted BIT NOT NULL ) Then you can achieve this with a unique index filtered to only include those Names that you wish to apply the constraint on. CREATE UNIQUE INDEX ix ON T(Name) WHERE IsDeleted = 'false'


3

I would look at your model first. Placing a unique key constraint on a non-unique column gets you into this kind of problem. What happens when you get a legitimate value like test title-2 but you've already used that value to resolve a collision on test title. If I had to resolve your problem, I would build a query for each unique key of the form: SELECT ...


3

Tough one, given that you have no access beyond a SELECT in db_A. So here's a thought, but it requires some pretty strict assumptions that may (or may not) be met: Requirements: All tables being sync'd have either: A timestamp (the more resolution the better) A unique, sequential ID All table rows, once sync'd, don't change. Alternatively, if a change ...


3

First, let me start with a warning. Be very careful when you start messing with the data on the slave. It can really ruin the integrity of the replication setup. That being said, if you are absolutely certain you don't want the slave to execute that particular statement, you can issue the following command on the slave: Edit by @RolandoMySQLDBA (Just added ...


3

This might be better placed on stack overflow. This isn't something you'll accomplish with MySQL. What you're talking about is referred to as 'stemming' in search. Similar to matching different conjugations of a regular word e.g. run => runs ,ran. I don't know of any such applications for proper names off hand but when you find one that will sit ...


3

You shouldn't try to do too many things with one data store. If you have a table for transactions, then just keep transactions in that table. Don't mix in scheduled future transactions with current transactions. Actual (historical) transactions will have different attributes (columns) than future, scheduled transactions. Keep the recurring/scheduled ...



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