Search code examples
mysqlmysql-error-1054

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'

Solution

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