Aunt Jamaima Aunt Jamaima - 5 months ago 28
MySQL Question

Unknown column in 'where not exists clause'

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'

Answer

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);