Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Blocker
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.16-galera
-
Fix Version/s: N/A
-
Component/s: Data Manipulation - Delete
-
Labels:None
-
Environment:debian 7
Description
create table `entreprise` ( `ent_id` int not null AUTO_INCREMENT, PRIMARY KEY (`ent_id`) ); create table `employee` ( `emp_id` int not null AUTO_INCREMENT, `room_id` int, PRIMARY KEY (`emp_id`) ); create table `room` ( `room_id` int not null AUTO_INCREMENT, `ent_id` int not null, PRIMARY KEY (`room_id`) );
delete from `employee` E where not exists(select 1 from `entreprise` a join `rooms` r on r.`ent_id` = a.`ent_id` and r.`room_id` = e.`room_id`);
Error column e.room_id is unknown
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
sorry for the late reply
yes, I was wrong about the capital.
When i execute this syntax select ... it is ok
select e.* from `employee` e
where not exists(select 1
from `entreprise` a
join `room` r on r.`ent_id` = a.`ent_id`
and r.`room_id` = e.`room_id`);
When i execute this syntax delete .... it is bad
Error on column e.`room_id`
delete from `employee` e
where not exists(select 1
from `entreprise` a
join `room` r on r.`ent_id` = a.`ent_id`
and r.`room_id` = e.`room_id`);
When i execute this syntax select ... it is ok
Please execute and paste the output from your client. The whole output, starting from the query and ending with the line "empty set".
There are several problems with your example.
First, the DELETE query as is ends with a syntax error:
This is because you use multi-table syntax, but only partially. Apparently, you meant something like:
It also fails, on the obvious reason:
So, in fact it should be this:
Now, it indeed fails with
There are two reasons for that. First, you are mixing up lowercase and uppercase alias names. Since you are on Wheezy, I assume you are running with case-sensitive table names, so you shouldn't do mix them. Instead, it should be like this:
But it still fails with the same error. Now we probably got to the problem which your report is actually about. ON clause can only refer to the operands in the JOIN, which `employee` is not. See http://dev.mysql.com/doc/refman/5.6/en/join.html – it's a long article, but it does actually say so, search for "can refer only".
Instead, this should work, and it does: