这个语句哪里有错误呢？

Question

delete from employees 
where departments.DEPARTMENT_NAME like '%ing'
and departments.DEPARTMENT_ID=employees.DEPARTMENT_ID运行结果
Error starting at line 1 in command:
delete from employees 
where departments.DEPARTMENT_NAME like '%ing'
and departments.DEPARTMENT_ID=employees.DEPARTMENT_ID
Error at Command Line:3 Column:4
Error report:
SQL Error: ORA-00904: "DEPARTMENTS"."DEPARTMENT_ID": 标识符无效
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action
我想删除已ing结尾的员工信息。

Answer 1

-- delete from employees  
where departments.DEPARTMENT_NAME like '%ing'
and departments.DEPARTMENT_ID=employees.DEPARTMENT_ID
-- 是关联另外一张表吧？delete from employees e
where department_id in ( select d.department_id 
                           from departments d 
                          where d.department_name like '%ing' );

Answer 2

错误信息不是已经说的很清楚了嘛，
QL Error: ORA-00904: "DEPARTMENTS"."DEPARTMENT_ID": 标识符无效
你DEPARTMENTS从哪里来的？

Answer 3

你这么写，必须不对的啊~~~你的DELETE语句有问题~~~应该是delete from employees  where exists (select 1 from departments where departments.DEPARTMENT_NAME like '%ing'
and departments.DEPARTMENT_ID=employees.DEPARTMENT_ID
  )  
这样应该可以。

Answer 4

肯定了。表都没列到from后面就引用人家的列。

Answer 5

delete from employees a where a.DEPARTMENT_ID in(select DEPARTMENT_ID from departments where regexp_like(DEPARTMENT_NAME,'ing'))

Answer 6

oracle里头还是用exists比in效率高点。delete from employees e where exists (select * from departments d where d.DEPARTMENT_NAME like '%ing' and d.DEPARTMENT_ID=e.DEPARTMENT_ID);这样就ok了。