Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: insert from a query?

    can i do this in mysql?

    tables t1 and t2 are exactly the same except that t2 will have rows in it that aren't in t1. also, i'm doing this through the mysql odbc 3.51 driver.

    pk_id is the primary key (an identity column from sql server).

    insert into t1 select * from t2 where pk_id not in (select pk_id from t1)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't use that syntax, but you can get the same effect using:
    Code:
    INSERT INTO t2
       SELECT t1.*
          FROM t1
          LEFT OUTER JOIN t2
             ON t2.pk_id = t1.pk_id
          WHERE  t2.pk_id IS NULL
    It is just a different way of thinking about the same thing.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a different way? i'll say

    cb1 wants to insert into t1 any t2 rows that t1 doesn't already have

    what you proposed inserts into t2 any t1 rows that t2 doesn't already have

    "just a different way of thinking", eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oooops! My bad.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no prob

    you are so infrequently wrong, i just wanted to make the correction and keep your record clean

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2004
    Posts
    4
    thanks for the help. i got it working properly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •