By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

I need help with selecting from 2 identical tables in 2 separate databases

P: n/a
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't
ask)

I have database Spring with table Students

I have database Summer with table Students

I am tasked to produce a query of all students in both tables with no
duplicates. No clue whatsoever.

I'm thinking an INTERSECT followed by 2 UNION SELECT statements, but
how far off am I? The tables are absolutely identical in every way
including indexes; the data MIGHT be identical (same data might be in
Spring.Students and Summer.Students), however, that won't always be the
case. The column Spring.Students.id will not share the same value as
Summer.Students.id as data can be entered within separate sequences
even if the data in both tables is absolutely identical.

Am I on the right track with INTERSECT/UNION or what do you recommend?

Thanx
Phil

Nov 23 '05 #1
Share this Question
Share on Google+
48 Replies


P: n/a
ph**************@gmail.com wrote:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't
ask)

I have database Spring with table Students

I have database Summer with table Students

I am tasked to produce a query of all students in both tables with no
duplicates. No clue whatsoever.

I'm thinking an INTERSECT followed by 2 UNION SELECT statements, but
how far off am I? The tables are absolutely identical in every way
including indexes; the data MIGHT be identical (same data might be in
Spring.Students and Summer.Students), however, that won't always be the
case. The column Spring.Students.id will not share the same value as
Summer.Students.id as data can be entered within separate sequences
even if the data in both tables is absolutely identical.

Am I on the right track with INTERSECT/UNION or what do you recommend?

Thanx
Phil


Well, this would be easier if you had both tables in one database. I
think it's more straightforward and portable to compare tables that
exist in the same database.

If necessary, create a third database and copy the Student tables from
Spring and Summer into the third database. You can use the mysqldump
tool to create a backup of a single table from each, then edit the two
dump files with a text editor to make sure the table names will be
distinct, then restore both dumps to the third database.

Here's one query to get the set of all students. Get all students who
exist in both Spring and Summer, then all students only in Spring, then
all students only in Summer. These three sets have no overlap.

(SELECT S1.student
FROM Spring AS S1 INNER JOIN Summer AS U1 ON S1.student = U1.student)
UNION
(SELECT S2.student
FROM Spring AS S2 LEFT OUTER JOIN Summer AS U2 ON S2.student = U2.student
WHERE U2.student IS NULL)
UNION
(SELECT U3.student
FROM Spring AS S3 RIGHT OUTER JOIN Summer AS U3 ON S3.student = U3.student
WHERE S3.student IS NULL)
ORDER BY student;

Regards,
Bill K.
Nov 23 '05 #2

P: n/a
The right thing is to use a UNION join:

select * from Spring.Students
union
select * from Summer.Students

Markus
Nov 23 '05 #3

P: n/a
Unfortunately that choice produced a MySQL syntax error.. I should have
mentioned we're using MySQL 3.23.58 in the current version and will
hopefully be upgrading to MySQL 4.0+ in the new version next year

Phil

Markus Popp wrote:
The right thing is to use a UNION join:

select * from Spring.Students
union
select * from Summer.Students

Markus


Nov 23 '05 #4

P: n/a
See below, thanx

Bill Karwin wrote:
ph**************@gmail.com wrote:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't
ask)

I have database Spring with table Students

I have database Summer with table Students

I am tasked to produce a query of all students in both tables with no
duplicates. No clue whatsoever.

I'm thinking an INTERSECT followed by 2 UNION SELECT statements, but
how far off am I? The tables are absolutely identical in every way
including indexes; the data MIGHT be identical (same data might be in
Spring.Students and Summer.Students), however, that won't always be the
case. The column Spring.Students.id will not share the same value as
Summer.Students.id as data can be entered within separate sequences
even if the data in both tables is absolutely identical.

Am I on the right track with INTERSECT/UNION or what do you recommend?

Thanx
Phil

Well, this would be easier if you had both tables in one database. I
think it's more straightforward and portable to compare tables that
exist in the same database.


Sadly, that is not an option. The client requested they be put into
separate databases. The explanation was about as "non-techie" as it
gets so I'll just spare you, but they plan to "rejoin" the tables into
one table in the new version of the application next year
If necessary, create a third database and copy the Student tables from
Spring and Summer into the third database. You can use the mysqldump
tool to create a backup of a single table from each, then edit the two
dump files with a text editor to make sure the table names will be
distinct, then restore both dumps to the third database.

The only problem with this approach is that the separate databases are
being currently used right now in a series of web application "patch"
scripts that were written in the zero-hour to placate the customer's
need to see the students separated by "spring" and "summer", so I
cannot yet rejoin them until the customer blesses the workflow
methodology of the updated web application, which won't be until next
year..
Here's one query to get the set of all students. Get all students who
exist in both Spring and Summer, then all students only in Spring, then
all students only in Summer. These three sets have no overlap.

(SELECT S1.student
FROM Spring AS S1 INNER JOIN Summer AS U1 ON S1.student = U1.student)
UNION
(SELECT S2.student
FROM Spring AS S2 LEFT OUTER JOIN Summer AS U2 ON S2.student = U2.student
WHERE U2.student IS NULL)
UNION
(SELECT U3.student
FROM Spring AS S3 RIGHT OUTER JOIN Summer AS U3 ON S3.student = U3.student
WHERE S3.student IS NULL)
ORDER BY student;

Yeah I tried the UNION statement but it failed due to a MySQL syntax
error. I forgot to mention that we're using MySQL 3.23.58 right now
and upgrading is "not an option at this time" (yep, next year.. blah
blah blah)
Regards,
Bill K.


*sigh* Can't use UNION so don't know what to do at this point

Phil

Nov 23 '05 #5

P: n/a
Ok, after re-reading (and getting my second cup of coffee) my brain
just kicked into gear and realized what you just said. In short, Phil
has left <stupid> mode.

That sounds doable, however, I will have to work on the means of
ensuring that the data from Spring.Students doesn't produce dups with
Summer.Students, and because UNION is not supported in MySQL 3.23.58
(I've tried to use it and consistently got syntax errors), I have
absolutely no idea how to do this :(

Phil

Bill Karwin wrote:
If necessary, create a third database and copy the Student tables from
Spring and Summer into the third database. You can use the mysqldump
tool to create a backup of a single table from each, then edit the two
dump files with a text editor to make sure the table names will be
distinct, then restore both dumps to the third database.


Nov 23 '05 #6

P: n/a
Incidentally, I tried your query, and, unfortunately, it produces a
syntax error in MySQL 3.23.58, which is really unfortunate considering
it's the easiest way to do this, and, at this point, don't have any
alternatives. :(

Phil
Bill Karwin wrote:
(SELECT S1.student
FROM Spring AS S1 INNER JOIN Summer AS U1 ON S1.student = U1.student)
UNION
(SELECT S2.student
FROM Spring AS S2 LEFT OUTER JOIN Summer AS U2 ON S2.student = U2.student
WHERE U2.student IS NULL)
UNION
(SELECT U3.student
FROM Spring AS S3 RIGHT OUTER JOIN Summer AS U3 ON S3.student = U3.student
WHERE S3.student IS NULL)
ORDER BY student;

Regards,
Bill K.


Nov 23 '05 #7

P: n/a
I'm afraid, it's not possible to do this with MySQL 3.23. You could only
query the tables separately and combine them on the client side.

If you consider updating (in fact, it's certainly a good choice to update),
I would recommand that you update straight to 5.0.x. Of course, there are
many details to take care of, but the manual stresses everything you need to
know to update from each release to another.

Markus
Nov 23 '05 #8

P: n/a
Hold on - there's one thing I forgot, there maybe is a solution.

If you use MyISAM tables you can use MERGE tables to combine 2 tables which
are identical in its structure. I'm not absolutely sure if MERGE tables are
supported in MySQL 3.23 (but I think so) and if it's possible to combine 2
tables that are in different databases. But in general, it works like this:

create table table_name ([column_definition as in the tables]) engine=merge
union=(table1, table2, ...)

Then this new table holds all records that are in each of the tables and you
can query them together.

Markus
Nov 23 '05 #9

P: n/a
Hmm.. I had no idea about that one, but this worked for me:

CREATE TABLE blah SELECT DISTINCT s.* FROM db1.table1 s LEFT OUTER JOIN
db2.table1 u WHERE lower(s.first_name) <> lower(u.first_name) AND
lower(s.last_name) <> lower(u.last_name)

Phil

Markus Popp wrote:
Hold on - there's one thing I forgot, there maybe is a solution.

If you use MyISAM tables you can use MERGE tables to combine 2 tables which
are identical in its structure. I'm not absolutely sure if MERGE tables are
supported in MySQL 3.23 (but I think so) and if it's possible to combine 2
tables that are in different databases. But in general, it works like this:

create table table_name ([column_definition as in the tables]) engine=merge
union=(table1, table2, ...)

Then this new table holds all records that are in each of the tables and you
can query them together.

Markus


Nov 23 '05 #10

P: n/a
> CREATE TABLE blah SELECT DISTINCT s.* FROM db1.table1 s LEFT OUTER JOIN
db2.table1 u WHERE lower(s.first_name) <> lower(u.first_name) AND
lower(s.last_name) <> lower(u.last_name)


This looks interesting - and makes sense.

Markus
Nov 23 '05 #11

P: n/a
ph**************@gmail.com wrote:
That sounds doable, however, I will have to work on the means of
ensuring that the data from Spring.Students doesn't produce dups with
Summer.Students, and because UNION is not supported in MySQL 3.23.58
(I've tried to use it and consistently got syntax errors), I have
absolutely no idea how to do this :(


The easiest path would be to install MySQL 4.1 on another computer (even
your home PC), transfer the table dumps from the client's computer to
yours, and then do the UNION query on MySQL 4.1. Then upload the
finished report of distinct students.

Repeat as necessary, every time they want an updated report of the
students. Keep track of the time spent doing these convoluted steps
carefully, so you can tell the client how much it is costing them to
_not_ have the tables in one database and up-to-date MySQL software. >:-)

Regards,
Bill K.
Nov 23 '05 #12

P: n/a
ph**************@gmail.com wrote:
Hmm.. I had no idea about that one, but this worked for me:

CREATE TABLE blah SELECT DISTINCT s.* FROM db1.table1 s LEFT OUTER JOIN
db2.table1 u WHERE lower(s.first_name) <> lower(u.first_name) AND
lower(s.last_name) <> lower(u.last_name)


Great work! I can never remember if a given RDBMS product & version can
support cross-database joins.

Regards,
Bill K.
Nov 23 '05 #13

P: n/a
Bad news.. the query doesn't work after all.. turns out that the query
only produces data from Spring.Students and from data found in both
Spring.Students and Summer.Students; all data unique to Summer.Students
is not copied over. :(

Phil

Bill Karwin wrote:
ph**************@gmail.com wrote:
Hmm.. I had no idea about that one, but this worked for me:

CREATE TABLE blah SELECT DISTINCT s.* FROM db1.table1 s LEFT OUTER JOIN
db2.table1 u WHERE lower(s.first_name) <> lower(u.first_name) AND
lower(s.last_name) <> lower(u.last_name)


Great work! I can never remember if a given RDBMS product & version can
support cross-database joins.

Regards,
Bill K.


Nov 23 '05 #14

P: n/a
ph**************@gmail.com wrote:
Bad news.. the query doesn't work after all.. turns out that the query
only produces data from Spring.Students and from data found in both
Spring.Students and Summer.Students; all data unique to Summer.Students
is not copied over. :(


It also treats John Smith and James Smith as the same person.

You're almost there.

First, get the folks who are in both spring and summer.

CREATE TABLE blah
SELECT DISTINCT s.*
FROM db1.table1 s INNER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);

Next, get the folks who are only in spring.

INSERT INTO blah
SELECT DISTINCT s.*
FROM db1.table1 s LEFT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE u.first_name IS NULL;

Next, get the folks who are only in summer.

INSERT INTO blah
SELECT DISTINCT u.*
FROM db1.table1 s RIGHT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE s.first_name IS NULL;

Regards,
Bill K.
Nov 23 '05 #15

P: n/a
*sigh* see below.. I HATE 3.23.58 THERE I SAID IT

Bill Karwin wrote:
ph**************@gmail.com wrote:
Bad news.. the query doesn't work after all.. turns out that the query
only produces data from Spring.Students and from data found in both
Spring.Students and Summer.Students; all data unique to Summer.Students
is not copied over. :(
It also treats John Smith and James Smith as the same person.

You're almost there.

First, get the folks who are in both spring and summer.

CREATE TABLE blah
SELECT DISTINCT s.*
FROM db1.table1 s INNER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);

Next, get the folks who are only in spring.

INSERT INTO blah
SELECT DISTINCT s.*
FROM db1.table1 s LEFT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE u.first_name IS NULL;


Syntax error near "blah SELECT DISTINCT..." you can't apparently do
"INSERT INTO [tableName] SELECT" in 3.23.58

I am looking into the option of CREATE TABLE blah ENGINE=MERGE
UNION=(table1, table2..)

Phil
Next, get the folks who are only in summer.

INSERT INTO blah
SELECT DISTINCT u.*
FROM db1.table1 s RIGHT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE s.first_name IS NULL;

Regards,
Bill K.


Nov 23 '05 #16

P: n/a

"Bill Karwin" <bi**@karwin.com> wrote in message
news:dl*********@enews2.newsguy.com...
ph**************@gmail.com wrote:
That sounds doable, however, I will have to work on the means of
ensuring that the data from Spring.Students doesn't produce dups with
Summer.Students, and because UNION is not supported in MySQL 3.23.58
(I've tried to use it and consistently got syntax errors), I have
absolutely no idea how to do this :(


The easiest path would be to install MySQL 4.1 on another computer (even
your home PC), transfer the table dumps from the client's computer to
yours, and then do the UNION query on MySQL 4.1. Then upload the
finished report of distinct students.

Repeat as necessary, every time they want an updated report of the
students. Keep track of the time spent doing these convoluted steps
carefully, so you can tell the client how much it is costing them to
_not_ have the tables in one database and up-to-date MySQL software. >:-)

Regards,
Bill K.

Ahh Bill,
you mean actually get paid for your work? What a concept and help the client
at the same time. You must be a revolutionary, or one of us old farts who
have been around a long time.
All the best,
Al K.
Nov 23 '05 #17

P: n/a
Sorry dude I got a syntax error on:

'ENGINE=MERGE UNION=(Spring.Students, Summer.Students)'

Phil

Markus Popp wrote:
Hold on - there's one thing I forgot, there maybe is a solution.

If you use MyISAM tables you can use MERGE tables to combine 2 tables which
are identical in its structure. I'm not absolutely sure if MERGE tables are
supported in MySQL 3.23 (but I think so) and if it's possible to combine 2
tables that are in different databases. But in general, it works like this:

create table table_name ([column_definition as in the tables]) engine=merge
union=(table1, table2, ...)

Then this new table holds all records that are in each of the tables and you
can query them together.

Markus


Nov 23 '05 #18

P: n/a
I failed to mention that the client is the US Federal Government, thus,
your "billable" option is utterly undoable. Which sucks :(

Phil

Al Kolff wrote:
"Bill Karwin" <bi**@karwin.com> wrote in message
news:dl*********@enews2.newsguy.com...
ph**************@gmail.com wrote:
That sounds doable, however, I will have to work on the means of
ensuring that the data from Spring.Students doesn't produce dups with
Summer.Students, and because UNION is not supported in MySQL 3.23.58
(I've tried to use it and consistently got syntax errors), I have
absolutely no idea how to do this :(


The easiest path would be to install MySQL 4.1 on another computer (even
your home PC), transfer the table dumps from the client's computer to
yours, and then do the UNION query on MySQL 4.1. Then upload the
finished report of distinct students.

Repeat as necessary, every time they want an updated report of the
students. Keep track of the time spent doing these convoluted steps
carefully, so you can tell the client how much it is costing them to
_not_ have the tables in one database and up-to-date MySQL software. >:-)

Regards,
Bill K.

Ahh Bill,
you mean actually get paid for your work? What a concept and help the client
at the same time. You must be a revolutionary, or one of us old farts who
have been around a long time.
All the best,
Al K.


Nov 23 '05 #19

P: n/a
Ok, at this point I'm completely out of ideas. The following SQL basic
syntax fails in MySQL 3.23.58:

UNION
INSERT INTO SELECT
ENGINE=MERGE UNION=(table1, table2)

I'm looking into data migration from 3.23.58 to a 4.0.17 engine we have
floating around here in the office, do the UNION and migrate the new
table back to 3.23.58, per your earlier suggestion, but that requires
the "powers that be" to bless such an action as I do not have the user
privileges to do so myself.

Phil

ph**************@gmail.com wrote:
*sigh* see below.. I HATE 3.23.58 THERE I SAID IT

Bill Karwin wrote:
ph**************@gmail.com wrote:
Bad news.. the query doesn't work after all.. turns out that the query
only produces data from Spring.Students and from data found in both
Spring.Students and Summer.Students; all data unique to Summer.Students
is not copied over. :(


It also treats John Smith and James Smith as the same person.

You're almost there.

First, get the folks who are in both spring and summer.

CREATE TABLE blah
SELECT DISTINCT s.*
FROM db1.table1 s INNER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);

Next, get the folks who are only in spring.

INSERT INTO blah
SELECT DISTINCT s.*
FROM db1.table1 s LEFT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE u.first_name IS NULL;


Syntax error near "blah SELECT DISTINCT..." you can't apparently do
"INSERT INTO [tableName] SELECT" in 3.23.58

I am looking into the option of CREATE TABLE blah ENGINE=MERGE
UNION=(table1, table2..)

Phil
Next, get the folks who are only in summer.

INSERT INTO blah
SELECT DISTINCT u.*
FROM db1.table1 s RIGHT OUTER JOIN db2.table1 u
ON LOWER(s.first_name) = LOWER(u.first_name)
AND LOWER(s.last_name) = LOWER(u.last_name);
WHERE s.first_name IS NULL;

Regards,
Bill K.


Nov 23 '05 #20

P: n/a
ph**************@gmail.com wrote:
Syntax error near "blah SELECT DISTINCT..." you can't apparently do
"INSERT INTO [tableName] SELECT" in 3.23.58


Okay, then create the table in the conventional way, by listing all the
fields and datatypes. Then fill it with data using INSERT INTO blah
SELECT...

Regards,
Bill K.
Nov 23 '05 #21

P: n/a
ph**************@gmail.com wrote:
I failed to mention that the client is the US Federal Government, thus,
your "billable" option is utterly undoable. Which sucks :(


Wow, the US Govt uses MySQL? Cool!

Well, at least make them understand why, when they ask you for a simple
report, it takes a few hours of manual steps, instead of being available
on demand in a canned query.

Regards,
Bill K.
Nov 23 '05 #22

P: n/a
As I said, it is possible that is doesn't work if the tables are in
different databases - I'm not sure about it.

Maybe try to write TYPE instead of ENGINE - TYPE is depricated in the
current versions, but maybe, in 3.23 it was the only valid option.

Markus
Nov 23 '05 #23

P: n/a
Well, following Bill's lead, I successfully suggested that the database
folder by copied from the server box with 3.23.58 to a server box with
4.0.17, which allows for UNION SELECT, and thus, problem should be
solved!

Phil

Markus Popp wrote:
As I said, it is possible that is doesn't work if the tables are in
different databases - I'm not sure about it.

Maybe try to write TYPE instead of ENGINE - TYPE is depricated in the
current versions, but maybe, in 3.23 it was the only valid option.

Markus


Nov 23 '05 #24

P: n/a
Data and structure copying from the 3.23.58 platform to the 4.0.17
platform is successful!

Now I have another problem: the query itself. How do I merge the 2
tables together into a new table?

Phil

Markus Popp wrote:
As I said, it is possible that is doesn't work if the tables are in
different databases - I'm not sure about it.

Maybe try to write TYPE instead of ENGINE - TYPE is depricated in the
current versions, but maybe, in 3.23 it was the only valid option.

Markus


Nov 23 '05 #25

P: n/a
Actually, I did 2 CREATE TABLE blah SELECT... to create copies of the
tables into the same database, and I still get the same syntax error
when attempting to combine:

CREATE TABLE blah TYPE=MERGE UNION=(blah1, blah2) SELECT * FROM blah

:(

This time using MySQL 4.0.17

Phil
Markus Popp wrote:
As I said, it is possible that is doesn't work if the tables are in
different databases - I'm not sure about it.

Maybe try to write TYPE instead of ENGINE - TYPE is depricated in the
current versions, but maybe, in 3.23 it was the only valid option.

Markus


Nov 23 '05 #26

P: n/a
Actually, I did 2 CREATE TABLE blah SELECT... to create copies of the
tables into the same database, and I still get the same syntax error
when attempting to combine:

CREATE TABLE blah TYPE=MERGE UNION=(blah1, blah2) SELECT * FROM blah1

:(

This time using MySQL 4.0.17

Phil
Markus Popp wrote:
As I said, it is possible that is doesn't work if the tables are in
different databases - I'm not sure about it.

Maybe try to write TYPE instead of ENGINE - TYPE is depricated in the
current versions, but maybe, in 3.23 it was the only valid option.

Markus


Nov 23 '05 #27

P: n/a
ph**************@gmail.com wrote:
Data and structure copying from the 3.23.58 platform to the 4.0.17
platform is successful!

Now I have another problem: the query itself. How do I merge the 2
tables together into a new table?


You should be able to use the UNION queries suggested earlier in this
thread. That was the point of copying the data to the server with the
newer version of MySQL, wasn't it?

Regards,
Bill K.
Nov 23 '05 #28

P: n/a
ph**************@gmail.com wrote:
Well, following Bill's lead, I successfully suggested that the database
folder by copied from the server box with 3.23.58 to a server box with
4.0.17, which allows for UNION SELECT, and thus, problem should be
solved!


I do not recommend copying data directories as a means of moving data
from one database server to another. While this seems to work most of
the time with MySQL (when using MyISAM tables), I have been bitten by it
on other RDBMS systems, because the data storage format can differ from
one version to another, or from one platform to another (e.g. Linux vs.
Windows vs. Solaris).

There's also a problem if there are pending writes to the tables,
because by copying the files directly from the filesystem, you don't get
data that are committed, but not written to disk yet. If you shut down
the MySQL service process, this should be safe, but often people copy
the data files without shutting down the MySQL service.

It should be safe to use the mysqldump tool to copy the data. You can
read the docs on this tool to find how to copy one individual table.

Regards,
Bill K.
Nov 23 '05 #29

P: n/a
This was all I could think of, it fails, and after 12 hours of trying I
can't think of anything else:

INSERT INTO spring_summer (
SELECT DISTINCT s.* FROM spring s
LEFT OUTER JOIN summer u ON lower(s.fname) <> lower(u.fname) AND
lower(s.lname) <> lower(u.lname)
);

INSERT INTO spring_summer (
SELECT DISTINCT s.* FROM summer s
LEFT OUTER JOIN spring u ON lower(s.fname) <> lower(u.fname) AND
lower(s.lname) <> lower(u.lname)
LEFT INNER JOIN spring_summer su ON su.id <> s.id
);

Produced a syntax error on the LEFT INNER JOIN clause in MySQL 4.0.17.
Sorry, no access to MySQL 4.1 :(

Phil
ph**************@gmail.com wrote:
Actually, I did 2 CREATE TABLE blah SELECT... to create copies of the
tables into the same database, and I still get the same syntax error
when attempting to combine:

CREATE TABLE blah TYPE=MERGE UNION=(blah1, blah2) SELECT * FROM blah1

:(

This time using MySQL 4.0.17

Phil
Markus Popp wrote:
As I said, it is possible that is doesn't work if the tables are in
different databases - I'm not sure about it.

Maybe try to write TYPE instead of ENGINE - TYPE is depricated in the
current versions, but maybe, in 3.23 it was the only valid option.

Markus


Nov 23 '05 #30

P: n/a
I tried, I can use UNION only with SELECT, the moment I try using it
within INSERT INTO or CREATE TABLE it produces a syntax error

INSERT INTO spring_summer (SELECT * FROM spring UNION SELECT * FROM
summer)

DROP TABLE spring_summer
CREATE TABLE spring_summer (SELECT * FROM spring UNION SELECT * FROM
summer)

Both of these produce syntax errors on the UNION clause

Phil

Bill Karwin wrote:
ph**************@gmail.com wrote:
Data and structure copying from the 3.23.58 platform to the 4.0.17
platform is successful!

Now I have another problem: the query itself. How do I merge the 2
tables together into a new table?


You should be able to use the UNION queries suggested earlier in this
thread. That was the point of copying the data to the server with the
newer version of MySQL, wasn't it?

Regards,
Bill K.


Nov 23 '05 #31

P: n/a
I'll remember that in the future, however, it was recommended by the
sys admin team because it'll always be Linux, always use MyISAM tables
and will never use RDBMS systems because it's not within our "scope".

Phil

Bill Karwin wrote:
ph**************@gmail.com wrote:
Well, following Bill's lead, I successfully suggested that the database
folder by copied from the server box with 3.23.58 to a server box with
4.0.17, which allows for UNION SELECT, and thus, problem should be
solved!


I do not recommend copying data directories as a means of moving data
from one database server to another. While this seems to work most of
the time with MySQL (when using MyISAM tables), I have been bitten by it
on other RDBMS systems, because the data storage format can differ from
one version to another, or from one platform to another (e.g. Linux vs.
Windows vs. Solaris).

There's also a problem if there are pending writes to the tables,
because by copying the files directly from the filesystem, you don't get
data that are committed, but not written to disk yet. If you shut down
the MySQL service process, this should be safe, but often people copy
the data files without shutting down the MySQL service.

It should be safe to use the mysqldump tool to copy the data. You can
read the docs on this tool to find how to copy one individual table.

Regards,
Bill K.


Nov 23 '05 #32

P: n/a
ph**************@gmail.com wrote:
Produced a syntax error on the LEFT INNER JOIN clause in MySQL 4.0.17.
Sorry, no access to MySQL 4.1 :(


There's no such thing as a LEFT INNER JOIN.
The LEFT and RIGHT keywords apply only to OUTER JOINs.

Regards,
Bill K.
Nov 23 '05 #33

P: n/a
>> Well, following Bill's lead, I successfully suggested that the database
folder by copied from the server box with 3.23.58 to a server box with
4.0.17, which allows for UNION SELECT, and thus, problem should be
solved!
I do not recommend copying data directories as a means of moving data
from one database server to another.


Upgrading the code in place (while just leaving the data there
unchanged) can cause trouble, too.
While this seems to work most of
the time with MySQL (when using MyISAM tables), I have been bitten by it
on other RDBMS systems, because the data storage format can differ from
one version to another, or from one platform to another (e.g. Linux vs.
Windows vs. Solaris).


I managed to destroy a couple of tables with phpMyAdmin by adding
a table comment. (phpMyAdmin is not at fault here, MySQL alone can
reproduce the problem, and it's probably my fault because I missed
an upgrade instruction of "dump all your databases, upgrade, then
restore them" somewhere along the way). Repair table didn't work.

It turns out that trying to add a table comment to MyISAM tables
version 7 with 5.0.15 destroys them. dumping/restoring the tables
produce a mixture of version 9 or 10. (You can see the version
number of a table with SHOW TABLE STATUS.)

Gordon L. Burditt
Nov 23 '05 #34

P: n/a
Gordon Burditt wrote:
It turns out that trying to add a table comment to MyISAM tables
version 7 with 5.0.15 destroys them. dumping/restoring the tables
produce a mixture of version 9 or 10. (You can see the version
number of a table with SHOW TABLE STATUS.)


Yikes! Good to know, Gordon. I try always to back up the database
before making any change to the schema or to the storage engine type, etc.

A system admin friend of mine told me, "always assume that the computer
is out to get you; not people in general, but _you_. It's personal!"

Creating backups is really, really easy in MySQL.
There's no reason not to do it before any change that is even slightly
risky (as long as you have enough disk space).

Regards,
Bill K.
Nov 23 '05 #35

P: n/a
> It turns out that trying to add a table comment to MyISAM tables
version 7 with 5.0.15 destroys them. dumping/restoring the tables
produce a mixture of version 9 or 10. (You can see the version
number of a table with SHOW TABLE STATUS.)


This is interesting, because new to me. Do you know if that problem still
exists in the new table versions? I also seem to have a mixture of version 9
and 10. Is that a problem?

Markus
Nov 23 '05 #36

P: n/a
I think, you don't even have to create a separate table (but of course, you
can). The syntax error might come from the parenthesis, so try:

INSERT INTO spring_summer SELECT * FROM spring UNION SELECT * FROM summer

and

DROP TABLE spring_summer
CREATE TABLE spring_summer SELECT * FROM spring UNION SELECT * FROM summer

Markus
Nov 23 '05 #37

P: n/a
> CREATE TABLE blah TYPE=MERGE UNION=(blah1, blah2) SELECT * FROM blah1

Don't use select after create table xxx (table_definition) type=merge
union=(...).

Inside a merge table, you will automatically have all the contents of the
tables that are specified inside the UNION part of the create statement. You
only have to make sure that you only specify MyISAM tables - this doesn't
work with other table types. And the table definition must be exactly the
same as from the tables specified inside the union clause.

Markus
Nov 23 '05 #38

P: n/a

Bill Karwin wrote:
ph**************@gmail.com wrote:
Produced a syntax error on the LEFT INNER JOIN clause in MySQL 4.0.17.
Sorry, no access to MySQL 4.1 :(
There's no such thing as a LEFT INNER JOIN.
The LEFT and RIGHT keywords apply only to OUTER JOINs.


Then why in the world have I been able to use it unabated until now? In
MySQL 3.23.58 - 4.1 with no syntax errors before now?

Phil

Regards,
Bill K.


Nov 23 '05 #39

P: n/a
WOW!!! After nearly four days straight, that worked!!!

DROP TABLE /*! IF EXISTS */ spring_summer;

CREATE TABLE /*! IF NOT EXISTS */ spring_summer SELECT * FROM
Spring.Students UNION SELECT * FROM Summer.Students

However, there is a problem that I just don't know how to fix with my
limited SQL knowledge.. there are multiple duplicate records (with
unique IDS) found in the new table; how do I get rid of those?

Phil

Markus Popp wrote:
I think, you don't even have to create a separate table (but of course, you
can). The syntax error might come from the parenthesis, so try:

INSERT INTO spring_summer SELECT * FROM spring UNION SELECT * FROM summer

and

DROP TABLE spring_summer
CREATE TABLE spring_summer SELECT * FROM spring UNION SELECT * FROM summer

Markus


Nov 23 '05 #40

P: n/a
Use the DISTINCT keyword:

select distinct col1, col2 from spring_summer; (list all the columns that
are non-unique, so leave out the ID column)

There are also ways to eliminate the duplicate records, but (with versions
before 4.1) that would require that you create another (temporary) table.
This looks something like this:

create table temp_table select min(id) as id, col1, col2 from spring_summer
group by col1, col2;

This produces a temporary table with all unique records with their lowest ID
(you could also use max(id) to get the highest ID).

Then you can join the temp_table with the spring_summer to find all records
that have another ID than those saved in the temp_table. Look at this:

mysql> select * from tbl;
+----+---+
| id | d |
+----+---+
| 1 | a |
| 2 | a |
| 3 | b |
| 4 | b |
| 5 | b |
| 6 | c |
| 7 | c |
+----+---+
7 rows in set (0.01 sec)

mysql> create table temp_table select min(id) as id, d from tbl group by d;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from temp_table;
+------+---+
| id | d |
+------+---+
| 1 | a |
| 3 | b |
| 6 | c |
+------+---+
3 rows in set (0.00 sec)

mysql> delete a from tbl a left join temp_table b on a.id = b.id where b.id
is null;
Query OK, 4 rows affected (0.00 sec)

I'm not sure if the last query works with MySQL 3.23. If it doesn't, it
would take some other extra steps (that's were we see the benefits of MySQL
4.1 and 5.0, where this all could be done in a single statement).

Markus
Nov 23 '05 #41

P: n/a
Sorry, the "distinct" keyword failed to produce truly unique records.
The Spring.Students table (and the Summer.Students table) has about 75
columns, 74 of them are identical, the "id" column is unique.

I did find a rather novel approach to this, however:

[SQL]

-- field `unique_key` will contain a 16-character random alphanumeric
string for repost protection
ALTER TABLE Spring.Students ADD UNIQUE (`unique_key`);

ALTER TABLE Summer.Students ADD UNIQUE (`unique_key`);

DROP TABLE /*! IF EXISTS */ AllStudents.spring_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spring_summer SELECT *
FROM Spring.Students LIMIT 1;

DELETE FROM AllStudents.spring_summer;

INSERT INTO AllStudents.spring_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students;

[/SQL]

This produces the following syntax error:

Syntax error near 'UNION SELECT * FROM Summer.Students'
What gives? It was working over the weekend and now fails? WHY?

Phil
Markus Popp wrote:
Use the DISTINCT keyword:

select distinct col1, col2 from spring_summer; (list all the columns that
are non-unique, so leave out the ID column)

There are also ways to eliminate the duplicate records, but (with versions
before 4.1) that would require that you create another (temporary) table.
This looks something like this:

create table temp_table select min(id) as id, col1, col2 from spring_summer
group by col1, col2;

This produces a temporary table with all unique records with their lowest ID
(you could also use max(id) to get the highest ID).

Then you can join the temp_table with the spring_summer to find all records
that have another ID than those saved in the temp_table. Look at this:

mysql> select * from tbl;
+----+---+
| id | d |
+----+---+
| 1 | a |
| 2 | a |
| 3 | b |
| 4 | b |
| 5 | b |
| 6 | c |
| 7 | c |
+----+---+
7 rows in set (0.01 sec)

mysql> create table temp_table select min(id) as id, d from tbl group by d;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from temp_table;
+------+---+
| id | d |
+------+---+
| 1 | a |
| 3 | b |
| 6 | c |
+------+---+
3 rows in set (0.00 sec)

mysql> delete a from tbl a left join temp_table b on a.id = b.id where b.id
is null;
Query OK, 4 rows affected (0.00 sec)

I'm not sure if the last query works with MySQL 3.23. If it doesn't, it
would take some other extra steps (that's were we see the benefits of MySQL
4.1 and 5.0, where this all could be done in a single statement).

Markus


Nov 23 '05 #42

P: n/a
Ok UPDATE:

We upgraded to MySQL 5.0 and I tried this, and this also failed to
function:

[SQL]
DROP TABLE /*! IF EXISTS */ AllStudents.spring_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spring_summer SELECT *
FROM OrigStudents.students LIMIT 1;

DELETE FROM AllStudents.spring_summer;

ALTER TABLE AllStudents.spring_summer ADD UNIQUE (unique_key);

INSERT INTO AllStudents.spring_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students;
[/SQL]

This in MySQL 5.0 throws an error after only inserting one record:
Duplicate entry 'asdfasdf' for key 1
I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!

Phil

Nov 23 '05 #43

P: n/a
>Ok UPDATE:

We upgraded to MySQL 5.0 and I tried this, and this also failed to
function:

[SQL]
DROP TABLE /*! IF EXISTS */ AllStudents.spring_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spring_summer SELECT *
FROM OrigStudents.students LIMIT 1;

DELETE FROM AllStudents.spring_summer;

ALTER TABLE AllStudents.spring_summer ADD UNIQUE (unique_key);

INSERT INTO AllStudents.spring_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students;


Use INSERT IGNORE here and let the errors happen, but it should
keep going..

Gordon L. Burditt
Nov 23 '05 #44

P: n/a
ph**************@gmail.com wrote:
I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!


You need to get help from the teacher. By the way, internet etiquette
generally frowns upon students trying to get help with their homework on
newsgroups.

Bill
Nov 23 '05 #45

P: n/a
Ok UPDATE:

We upgraded to MySQL 5.0 and I tried this, and this also failed to
function:

[SQL]
DROP TABLE /*! IF EXISTS */ AllStudents.spring_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spring_summer SELECT *
FROM OrigStudents.students LIMIT 1;

DELETE FROM AllStudents.spring_summer;

ALTER TABLE AllStudents.spring_summer ADD UNIQUE (unique_key);

INSERT INTO AllStudents.spring_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students;
[/SQL]

This in MySQL 5.0 throws an error after only inserting one record:
Duplicate entry 'asdfasdf' for key 1
I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!

Phil

Nov 23 '05 #46

P: n/a
There is no teacher, class, whatever. This is work and it's due Monday
AM!

Phil
Bill Karwin wrote:
ph**************@gmail.com wrote:
I can't figure this out, it's literally due AM Monday; I've been at
this for a week now (it was due earlier this week) and I can't combine
two tables into 1 with unique data!


You need to get help from the teacher. By the way, internet etiquette
generally frowns upon students trying to get help with their homework on
newsgroups.

Bill


Nov 23 '05 #47

P: n/a
ph**************@gmail.com wrote:
INSERT INTO AllStudents.spring_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students;

This in MySQL 5.0 throws an error after only inserting one record:
Duplicate entry 'asdfasdf' for key 1


Well, this means that there are some entries in Spring.Students that
also exist in Summer.Students. Therefore you get a conflict as soon as
the insert reaches the first duplicate (maybe the first record returned
by the second half of the UNION).

At the beginning of this thread I suggested a query construction that
should work. Here it is again, with the table names matching your query
above.

INSERT INTO AllStudents.spring_summer
(SELECT S1.*
FROM Spring.Students AS S1 INNER JOIN Summer.Students AS U1 ON
S1.unique_key = U1.unique_key)
UNION
(SELECT S2.*
FROM Spring.Students AS S2 LEFT OUTER JOIN Summer.Students AS U2 ON
S2.unique_key = U2.unique_key
WHERE U2.unique_key IS NULL)
UNION
(SELECT U3.*
FROM Spring.Students AS S3 RIGHT OUTER JOIN Summer.Students AS U3 ON
S3.unique_key = U3.unique_key
WHERE S3.unique_key IS NULL);

Regards,
Bill K.
Nov 24 '05 #48

P: n/a
You know the old adage, "Ignore the problem and it'll go away"? That's
exactly what I did, and everything worked:

DROP TABLE AllStudents.students;
CREATE TABLE AllStudents.students SELECT * FROM Spring.Students LIMIT
1;
ALTER TABLE AllStudents.students ADD UNIQUE (email);
ALTER TABLE AllStudents.students ADD UNIQUE (unique_key);
INSERT IGNORE INTO AllStudents.students SELECT * FROM Spring.Students
UNION DISTINCT SELECT * FROM Summer.Students;

That did the trick! In short, enforce uniqueness on 2 of the columns
that should be unique, which ensures "duplicates" are never added while
warnings are thrown out, so that all unique records are combined into
one table!

The only stipulation was that we had to do an enforced upgrade to MySQL
5.0 to ensure this would work.

Phil

Bill Karwin wrote:
ph**************@gmail.com wrote:
INSERT INTO AllStudents.spring_summer SELECT * FROM Spring.Students
UNION SELECT * FROM Summer.Students;

This in MySQL 5.0 throws an error after only inserting one record:
Duplicate entry 'asdfasdf' for key 1


Well, this means that there are some entries in Spring.Students that
also exist in Summer.Students. Therefore you get a conflict as soon as
the insert reaches the first duplicate (maybe the first record returned
by the second half of the UNION).

At the beginning of this thread I suggested a query construction that
should work. Here it is again, with the table names matching your query
above.

INSERT INTO AllStudents.spring_summer
(SELECT S1.*
FROM Spring.Students AS S1 INNER JOIN Summer.Students AS U1 ON
S1.unique_key = U1.unique_key)
UNION
(SELECT S2.*
FROM Spring.Students AS S2 LEFT OUTER JOIN Summer.Students AS U2 ON
S2.unique_key = U2.unique_key
WHERE U2.unique_key IS NULL)
UNION
(SELECT U3.*
FROM Spring.Students AS S3 RIGHT OUTER JOIN Summer.Students AS U3 ON
S3.unique_key = U3.unique_key
WHERE S3.unique_key IS NULL);

Regards,
Bill K.


Nov 28 '05 #49

This discussion thread is closed

Replies have been disabled for this discussion.