#2161 (ok 2.11) Wrong primary key used (results with subquery)
I had a child table that I wanted to add a foreign key
to. It told me there was a missing row in the parent
table so I went about with a query that selected all
child items where the parent row does not exist, and
then I wanted to manually delete each row
CREATE TABLE `parent` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`test` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;
CREATE TABLE `child` (
`parent_id` INT UNSIGNED NOT NULL ,
`id` INT UNSIGNED NOT NULL ,
PRIMARY KEY ( `parent_id` , `id` )
) ENGINE = MYISAM ;
INSERT INTO `child` ( `parent_id` , `id` )
VALUES (
'1', '1'
);
select * from child where not exists (select id from
parent where child.parent_id = parent.id);
When I run the last query in the SQL tab, I get the one
row, as expected. However, if I try to delete that row
from the results, it gives me this error:
SQL query:
DELETE FROM `parent` WHERE `parent_id` =1 AND `id` =1
LIMIT 1
MySQL said: Documentation
#1054 - Unknown column 'parent_id' in 'where clause'
Another thing I noticed, when I run the select query,
the table at the top (like the current "active" table
in PMA) changes to "parent" instead of whatever other
table might have been active at the time, so obviously
it has something to do with the SQL parser or whatever,
although the symptoms of it are that it assumes the
primary key of the results is a different table than it
really is...
Logged In: YES
user_id=210714
Originator: NO
Indeed the parser does not (yet) support subqueries.