oracl查询基础5.pptVIP

* * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. [Title of the course] You can use the INSERT statement to add rows to a table where the values are derived from existing tables. In place of the VALUES clause, you use a subquery. Syntax INSERT INTO table [ column (, column) ] subquery; In the syntax: table is the table name column is the name of the column in the table to populate subquery is the subquery that returns rows into the table The number of columns and their data types in the column list of the INSERT clause must match the number of values and their data types in the subquery. To create a copy of the rows of a table, use SELECT * in the subquery. INSERT INTO EMPL3 SELECT * FROM employees; For more information, see Oracle Database 10g SQL Reference. * * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. [Title of the course] You can use a subquery in place of the table name in the INTO clause of the INSERT statement. The select list of this subquery must have the same number of columns as the column list of the VALUES clause. Any rules on the columns of the base table must be followed in order for the INSERT statement to work successfully. For example, you cannot put in a duplicate employee ID or leave out a value for a mandatory NOT NULL column. This application of subqueries helps avoid having to create a view just for performing an INSERT. * * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. [Title of the course] The example shows the results of the subquery that was used to identify the table for the INSERT statement. * * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. [Title of the course] You can use subqueries in UPDATE statements to update rows in a table. The example on the slide updates the EMPL3 table based on the values from the EMPLOYEES table. It changes the department number of all employees with employee 200’s job ID to employee 100’s current department numb