Query two different databases for differences
I have two different databases on the same 5.0 server that have the same tables and structure. They also have very nearly the same data. For one of the tables I'd like to run a query to find the records that are present in one database but not the other. Is this possible and what would such a query look like? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query two different databases for differences
James;
This finds common rows.
Eh!? ... HAVING COUNT(*)=1 returns ONLY pairs that are different:
drop table if exists a,b;
create table a(i int,j int,k int);
insert into a values(1,10,100),(2,20,200),(3,30,300);
create table b select * from a;
update b set k=301 where k=300;
select * from a;
+--+--+--+
| i| j| k|
+--+--+--+
|1 | 10 | 100 |
|2 | 20 | 200 |
|3 | 30 | 300 |
+--+--+--+
select * from b;
+--+--+--+
| i| j| k|
+--+--+--+
|1 | 10 | 100 |
|2 | 20 | 200 |
|3 | 30 | 301 |
+--+--+--+
SELECT
MIN(TableName) as TableName, i,j,k
FROM (
SELECT 'Table a' as TableName, a.i, a.j, a.k
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.i, b.j, b.k
FROM b
) AS tmp
GROUP BY i,j,k
HAVING COUNT(*) = 1
ORDER BY i;
+---+--+--+--+
| TableName | i| j| k|
+---+--+--+--+
| Table a |3 | 30 | 300 |
| Table b |3 | 30 | 301 |
+---+--+--+--+
PB
-
James Eaton wrote:
From: Peter Brawley
I'd like to run a query to find the records that
are present in one database but not the other.
See 'Compare data in two tables' at
http://www.artfulsoftware.com/infotree/queries.php.
Thanks. That's a start.
SELECT
MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;
This finds common rows. That doesn't help when the tables have about
20,000 rows and most are the same. How do I invert the query so that
I can find the 'uncommon' rows? Second, the primary key ('id' in the
example) values do not match, so how/where in the query can you
specify how to match rows from the two tables using other columns?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query two different databases for differences
From: Peter Brawley
I'd like to run a query to find the records that
are present in one database but not the other.
See 'Compare data in two tables' at
http://www.artfulsoftware.com/infotree/queries.php.
Thanks. That's a start.
SELECT
MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;
This finds common rows. That doesn't help when the tables have about
20,000 rows and most are the same. How do I invert the query so that I
can find the 'uncommon' rows? Second, the primary key ('id' in the
example) values do not match, so how/where in the query can you specify
how to match rows from the two tables using other columns?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query two different databases for differences
On Feb 11, 2008 7:27 PM, James Eaton [EMAIL PROTECTED] wrote:
SELECT
MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;
This finds common rows. That doesn't help when the tables have about
20,000 rows and most are the same. How do I invert the query so that I
can find the 'uncommon' rows? Second, the primary key ('id' in the
example) values do not match, so how/where in the query can you specify
how to match rows from the two tables using other columns?
SELECT A.message_number
FROM message_table A
LEFT JOIN delivery_table B
ON A.message_number = B.message_number
WHERE B.message_number is NULL
AND A.deleted = 0;
Will select every message_number that appears in A but not B and A's deleted
field equals 0.
--
We are all slave to our own paradigm. -- Joshua Williams
If the letters PhD appear after a person's name, that person will remain
outdoors even after it's started raining. -- Jeff Kay
Re: Query two different databases for differences
James I'd like to run a query to find the records that are present in one database but not the other. See 'Compare data in two tables' at http://www.artfulsoftware.com/infotree/queries.php. PB James Eaton wrote: I have two different databases on the same 5.0 server that have the same tables and structure. They also have very nearly the same data. For one of the tables I'd like to run a query to find the records that are present in one database but not the other. Is this possible and what would such a query look like? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
