UPDATE

用子查询来更新数据

UPDATE employees
SET salary = (
-- 这里其实是一个联合子查询
    SELECT
        employee2.salary
    FROM
        employee2
    WHERE
        employee2.employee_id = employees.employee_id
    AND employees.salary != employees2.salary
)
WHERE
    department_id = 90;

使用SELECT来定义表和列从而更新数据

UPDATE (
/*这里可以将SELECT生成的表格视为一个指针表
SELECT生成的这个表里的cell
其实是对应到了源表中的cell的
我UPDATE这个生成的表中的cell
就是UPDATE了源表中的cell */
    SELECT
        e1.salary,
        e2.salary new_sal
    FROM
        employees e1,
        employees2 e2
    WHERE
        e1.employee_id = e2.employee_id
    AND e1.department_id = 90
)
SET salary = new_sal;

更新多列

UPDATE employees
SET (salary, commission_pct) = (
    SELECT
        employees2.salary,
        comm_pct
    FROM
        employees2
    WHERE
        employee2.employee_id = employees.employee_id
    AND employees.salary != employees2.salary
)
WHERE
    department_id = 90;