Using a Subquery in an INSERT Statement

INSERT INTO
(SELECT employee_id, last_name,
email, hire_date, job_id, salary,
department_id
FROM employees
WHERE department_id = 50)
VALUES (99999, 'Taylor', 'DTAYLOR',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);

1 row created.


Using the WITH CHECK OPTION Keyword on DML Statements
• A subquery is used to identify the table and columns of the DML statement.
• The WITH CHECK OPTION keyword prohibits you from changing rows that are not in the subquery.

INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
INSERT INTO
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation