Unknown column in 'where not exists clause'

Question

There are lots of answers for "where not exists" type inserts that expect you to specify the columns to insert. I have over 400 columns, and the two tables have identical schema.

There are also lots of answers for unknown column, but not one that I can find in an insert where not exists statement. I can't understand the problem and hope someone here can help.

Here is the sql fiddle http://sqlfiddle.com/#!9/70a34b/1

Here is one of several queries I've tried:

INSERT INTO test1 

SELECT * FROM test2 b 

WHERE NOT EXISTS(SELECT * FROM test2 c WHERE test1.go = c.go);

I get the error Unknown column 'test1.go' in 'where clause'

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Just flip your table names around. Remember, you're trying to insert into table1 FROM table2 where the values are not already in TABLE1.

Change the query to:

 INSERT INTO test1 
 SELECT * FROM test2 b 
 WHERE NOT EXISTS(SELECT * FROM test1 c WHERE c.go = b.go);

Unknown column in 'where not exists clause'

Sign up