Oracle SQL Update Select

Question

正在为我即将进行的测试进行模拟测试，我一直被困在更新选择部分。

首先，这是我到目前为止的代码：

    CREATE TABLE UNIT
(unit_id            char(3)     Primary key,
 unit_name          VARCHAR(30) NOT NULL,
 valid_till         DATE        Check(valid_till > '01-Apr-2016'),
 salary             NUMBER(7,2),
 constraint unit_n_unq unique (unit_name));

CREATE TABLE STAFF
(staff_id           NUMBER(3)   Primary key,
 unit_id            Char(3)     References unit(unit_id),
 unit_name          VARCHAR(30) references unit(unit_name),
 name               VarChar(25) NOT NULL,
 dob                DATE,
 gender             Char(1)     Check(gender IN ('M', 'F', 'X')),
 period             NUMBER(2)   Check(period  <= '52'),
 pay                NUMBER(6,2),
 totalpay           NUMBER(10,2));
CREATE SEQUENCE staff_id_seq
    START WITH 100
    INCREMENT BY 1;

CREATE TRIGGER staff_id_trg
    BEFORE INSERT ON STAFF
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN
        SELECT staff_id_seq.NEXTVAL INTO :NEW.staff_id FROM DUAL;
    END;
    /


INSERT INTO UNIT (unit_id, unit_name, valid_till, salary)
VALUES ('abc', 'dis1', '01-Jan-2017', '56.34');
INSERT INTO UNIT (unit_id, unit_name, valid_till, salary)
VALUES ('def', 'dis2', '02-Jan-2017', '56.34');
INSERT INTO UNIT (unit_id, unit_name, valid_till, salary)
VALUES ('str', 'dis3', '03-Jan-2017', '56.34');

INSERT INTO STAFF (staff_id, unit_id, name, dob, gender, period, unit_name)
    SELECT 123, unit.unit_id,'steve', '01-Jan-2016', 'M', 52, unit.unit_name
    FROM UNIT
    WHERE unit.unit_name = 'dis1';
INSERT INTO STAFF (staff_id, unit_id, name, dob, gender, period, unit_name)
    SELECT 123, unit.unit_id,'bob', '01-Jan-2016', 'M', 52, unit.unit_name
    FROM UNIT
    WHERE unit.unit_name = 'dis2';
INSERT INTO STAFF (staff_id, unit_id, name, dob, gender, period, unit_name)
    SELECT 123, unit.unit_id,'ethan', '01-Jan-2016', 'M', 52, unit.unit_name
    FROM UNIT
    WHERE unit.unit_name = 'dis3';
INSERT INTO STAFF (staff_id, unit_id, name, dob, gender, period, unit_name)
    SELECT 123, unit.unit_id,'rock', '01-Jan-2016', 'M', 52, unit.unit_name
    FROM UNIT
    WHERE unit.unit_name = 'dis1';
INSERT INTO STAFF (staff_id, unit_id, name, dob, gender, period, unit_name)
    SELECT 123, unit.unit_id,'boulder', '01-Jan-2016', 'M', 52, unit.unit_name
    FROM UNIT
    WHERE unit.unit_name = 'dis2';

 UPDATE staff 
    set (staff.pay) = (
    select unit.salary
    FROM unit
    WHERE unit.salary = unit.salary
);

我想做的就在底部。我想使用更新选择更新"员工"表中的"支付"行。这个新的付薪列需要是付薪 = 单位.工资/52。

寻求一些帮助。干杯。

Answer 1

我认为您需要一个相关的子查询。像这样：

update staff
set staff.pay = (
        select salary / 52
        from unit
        where unit.unit_id = staff.unit_id
        );