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

[phillip.s.powell@gmail.com]

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

Ok UPDATE:

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

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

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spr ing_summer SELECT *
FROM OrigStudents.st udents LIMIT 1;

DELETE FROM AllStudents.spr ing_summer;

ALTER TABLE AllStudents.spr ing_summer ADD UNIQUE (unique_key);

INSERT INTO AllStudents.spr ing_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

[phillip.s.powell@gmail.com]

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

There is no teacher, class, whatever. This is work and it's due Monday
AM!

Phil
Bill Karwin wrote:[color=blue]
> phillip.s.powel l@gmail.com wrote:[color=green]
> > 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![/color]
>
> 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[/color]

[Bill Karwin]

Re: I need help with selecting from 2 identical tables in 2 separatedatabas es

phillip.s.powel l@gmail.com wrote:[color=blue]
> INSERT INTO AllStudents.spr ing_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

[/color]

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

[phillip.s.powell@gmail.com]

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

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.stu dents;
CREATE TABLE AllStudents.stu dents SELECT * FROM Spring.Students LIMIT
1;
ALTER TABLE AllStudents.stu dents ADD UNIQUE (email);
ALTER TABLE AllStudents.stu dents ADD UNIQUE (unique_key);
INSERT IGNORE INTO AllStudents.stu dents 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:[color=blue]
> phillip.s.powel l@gmail.com wrote:[color=green]
> > INSERT INTO AllStudents.spr ing_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

[/color]
>
> 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.spr ing_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.[/color]