在insert中使用子查询
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.
这个是什么意思呢?好像仅仅是往employees里面插入了一条数据。我试验的结果是这样,暂时还没有发现他的作用,如果作用仅仅是往employees里面插入一条数据,为什么要用子查询呢?
请大牛们解释下。谢谢
解答:
=========================================================================================
DROP TABLE t;
CREATE TABLE t(ID NUMBER PRIMARY KEY,sal NUMBER);
--成功,随便插入,当然有其他约束除外
INSERT INTO (SELECT ID,sal FROM t WHERE sal>5000)
VALUES(1,4000);
--加了with check option则保证了只能插入where条件的
INSERT INTO (SELECT ID,sal FROM t WHERE sal>5000 WITH CHECK OPTION)
VALUES(1,4000);
--成功
INSERT INTO (SELECT ID,sal FROM t WHERE sal>5000 WITH CHECK OPTION)
VALUES(1,6000);