Can someone explain why I get an error Code 1222 (The used SELECT statements have a different number of columns) after I run this query?
INSERT IGNORE INTO table1(id1)
SELECT id2 FROM table2;
It's pretty obvious that number of columns are the same, so real issue must be somewhere else. But where? Fields are the exact same:
`id2` int(11) NOT NULL COMMENT 'blabla'
Only difference is the DB engine (MyISAM on table2, InnoDB on table1), but it can't be linked, because it works like a charm if I add more columns in my INSERT/SELECT without this one.
Any ideas? Thx.
OK, I finally found what was going wrong: a trigger. Basically the number of variables I had at some point did not match the number of columns. (https://dev.mysql.com/doc/refman/5.5/en/stored-program-variables.html).
It seems pretty obvious now that I found the cause, but honestly, MySQL could be more explicit. It's not like I have a single and/or simple query triggered behind the scene (this one was especially a bit complex actually).
For the record error occurred on this field, because that's the one I had forgotten to declare.
Thx for your time guys.