UPDATE copy_emp
SET department_id = 110;
22 rows updated.
SQL> ed
Wrote file afiedt.buf
1* update emp set sal = 1800 , deptno = 20where ename='SMITH'
SQL> /
在UPDATE语句中使用子查询
更新 114号员工的工作和工资使其与 205号员工相同
UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
1 row updated.
在 UPDATE 中使用子查询,使更新基于另一个表中的 数据。
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);
1 row updated.
更新中的数据完整性错误
UPDATE employees
SET department_id = 55WHERE department_id = 110;
======
UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent keynotfound