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

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • phillip.s.powell@gmail.com

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

    Comment

    • phillip.s.powell@gmail.com

      #47
      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]

      Comment

      • Bill Karwin

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

        Comment

        • phillip.s.powell@gmail.com

          #49
          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]

          Comment

          Working...