insert from a query?

cb1 · #1 (**permalink**) 05-03-04, 16:32

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)

Pat Phelan · #2 (**permalink**) 05-03-04, 16:39

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

r937 · #3 (**permalink**) 05-03-04, 21:46

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