Oracle_071_lesson_p17

starxzj

Manipulating Data by Using Subqueries 使用子查询控制数据
　　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, city, 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

• 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 E
　　WHERE employee_id =
　　(SELECT employee_id
　　FROM   emp_history
　　WHERE  employee_id = E.employee_id);