[MDEV-7633] Delete .... not exists Created: 2015-02-25  Updated: 2015-02-26  Resolved: 2015-02-25

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete
Affects Version/s: 10.0.16-galera
Fix Version/s: N/A

Type: Bug Priority: Blocker
Reporter: hervé Assignee: Unassigned
Resolution: Not a Bug Votes: 0
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

 Comments   
Comment by Elena Stepanova [ 2015-02-25 ]

There are several problems with your example.

First, the DELETE query as is ends with a syntax error:

MariaDB [test]> 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 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'E where not exists(select 1 from `entreprise` a join `rooms` r on r.`ent_id` = a' at line 1

This is because you use multi-table syntax, but only partially. Apparently, you meant something like:

delete E 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`);

It also fails, on the obvious reason:

ERROR 1146 (42S02): Table 'test.rooms' doesn't exist

So, in fact it should be this:

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

Now, it indeed fails with

ERROR 1054 (42S22): Unknown column 'e.room_id' in 'on clause'

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:

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

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:

delete E from `employee` E where not exists(select 1 from `entreprise` a join `room` r on r.`ent_id` = a.`ent_id` where r.`room_id` = E.`room_id`);

Comment by hervé [ 2015-02-26 ]

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

Comment by Elena Stepanova [ 2015-02-26 ]

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".

Comment by hervé [ 2015-02-26 ]

ok, but too later

Generated at Thu Feb 08 07:21:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.