INSERT using SELECT LEFT JOIN

Question

I've got a query that executes just fine until I try to insert the results into a new table. After searching all over and changing things here and there that don't really make much of a difference from my original query, I'm hoping someone here can figure out what's going on.

INSERT INTO new_table(col1, col2, col3, col4)
SELECT t1.col1, t2.col2, t1.col3, t1.col4
FROM table1 t1 LEFT JOIN table2 t2 ON (t1.col1=t2.col1)
WHERE (t2.col1 IS NOT NULL)

When I run this query, the new table is filled as if I had just said INSERT INTO new_table SELECT * FROM table1, which obviously cuts out any of the matched values from table2. As I said, running this query without the INSERT statement returns accurate results. Any ideas?

Answer 1

why not just

CREATE TABLE new_Table as SELECT t1.col1, t2.col2, t1.col3, t1.col4
FROM table1 t1 LEFT JOIN table2 t2 ON (t1.col1=t2.col1)
WHERE (t2.col1 IS NOT NULL)

your indexes won't be there, but this looks like a summary table based on the left join...