Oracle_071_lesson_p17

you should be able to:
1、Use subqueries to manipulate data
2、Insert values by using a subquery as a target
3、Use the WITH CHECK OPTION keyword on DML statements
4、Use correlated subqueries to update and delete rows

You can use subqueries in data manipulation language (DML) statements to:
1、Retrieve data by using an inline view
2、Copy data from one table to another
3、Update data in one table based on the values of another table
4、Delete rows from one table based on rows in another table

INSERT INTO (SELECT l.location_id,l.city,l.country_id
FROM loc l
JOIN countries c
ON(l.country_id = c.country_id)
JOIN regions USING(region_id)
WHERE region_name = ‘Europe‘)
VALUES (3300,‘Cardiff‘,‘UK‘);

SELECT location_id,city,country_id
FROM loc;

Using the WITH CHECK OPTION Keyword on DML Statements

INSERT INTO ( SELECT location_id,country_id
FROM loc
WHERE country_id IN
(SELECT country_id
FROM countries
NATURAL JOIN regions
WHERE region_name = ‘Europe‘)
WITH CHECK OPTION )
VALUES (3600,‘Washington‘,‘US‘);
Error report:
SQL Error: ORA-01402: view WITH CHECK OPTION where-clause violation

1402. 00000 - "view WITH CHECK OPTION where-clause violation"
      Cause:
      Action:

Correlated UPDATE
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);

ALTER TABLE empl6
ADD(department_name VARCHAR2(25));

UPDATE empl6 e
SET department_name =
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);

Correlated DELETE
DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

DELETE FROM empl6 EWHERE employee_id = (SELECT employee_idFROM emp_history WHERE employee_id = E.employee_id);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!