SQL base subquery, multi-table Insert, merge statement, track time data changes (20)

Working with data using sub-queries

You can use Data Manipulation language (DML) statements in a subquery:

Retrieving data using inline views

Copy data from one table to another

Update data in a table based on the value of another table

Delete rows from a table based on the value of another table

Retrieving data as a data source using a subquery

Select Department_name, City from departments

Natural Join (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 ');

Inserting data using a subquery as a target

Insert INTO (select l.location_id, L.city, l.country_id from locations l

Join countries C

On (l.country_id = c.country_id)

Join regions using (region_id)

where Region_name = ' Europe ')

VALUES (3300, ' Cardiff ', ' UK ');

Using the WITH CHECK OPTION in a DML statement

The WITH CHECK OPTION keyword prevents the subquery from being changed in the row.

Overview of the default features displayed

• Use the default keyword to set the field defaults.

• Allows the user to control when the default values are applied to the data

• Default values can be explicitly used in INSERT and UPDATE statements

Use an explicit default value

INSERT vs. DEFAULT:

Insert into DEPTM3 (department_id, Department_name, manager_id) VALUES (' Engineering ', default);

UPDATE and DEFAULT:

Update DEPTM3 Set manager_id = default where department_id = 10;

Copy rows from another table

• Sub-query for INSERT statement:

INSERT into Sales_reps (ID, name, salary, commission_pct)

Select employee_id, last_name, salary, commission_pct

From Employees

where job_id like '%rep% ';

• Do not use the VALUES clause

• The INSERT clause matches the number of columns and types of the subquery

Use the following types to complete multiple table insertions:

– Unconditional INSERT

– Rotate INSERT

– Conditional INSERT All

– Conditional INSERT First

Insert All

into target_a values (...,...)

into Target_b values (...,...)

into Target_c values (...,...)

Select ...

From Sourcetab

where ...;

Multi-Table query:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8F/38/wKiom1jX5cODE2nzAADCLi6us0w436.jpg "title=" Qq20170326222330.jpg "width=" 720 "height=" 442 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:720PX;HEIGHT:442PX; "alt = "Wkiom1jx5code2nzaadcli6us0w436.jpg"/>

The Multi-table insertion function is as follows:

• Use Insert ... The SELECT statement inserts rows into multiple tables as a single DML statement.

• The Multiple-table INSERT statement used in a data warehouse system writes the source data for one or more operations to a set of target tables.

• They provide significant performance improvements:

– Single DML statement with multiple table inserts ... SELECT statement

– Single DML statement with use IF ... Then syntax to complete multiple table insertions

Types of multiple-table INSERT statements

The following are the different types of multiple-table INSERT statements:

• Unconditional INSERT

• Rotate INSERT

• Conditional INSERT All

• Conditional INSERT First

Multi-table INSERT syntax

Insert [Conditional_insert_clause]

[Insert_into_clause Values_clause] (subquery)

Conditional INSERT clauses:

[all| First]

[When condition then] [Insert_into_clause Values_clause]

[ELSE] [Insert_into_clause Values_clause]

Unconditional INSERT All

Insert All

into sal_history values (empid,hiredate,sal)

into mgr_history values (empid,mgr,sal)

Select employee_id empid, Hire_date hiredate,

Salary Sal, manager_id Mgr

From Employees

where employee_id > 200;

Conditional Insert All: example

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8F/36/wKioL1jX5wiz0iMcAABqAzWKp2M786.jpg "title=" Qq20170326222330.jpg "width=" 720 "height=" 419 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:720PX;HEIGHT:419PX; "alt = "Wkiol1jx5wiz0imcaabqazwkp2m786.jpg"/>

Conditional Insert All

Insert All

When HireDate < ' 01-jan-95 ' then

into emp_history values (empid,hiredate,sal)

When comm are not null and then

into emp_sales values (empid,comm,sal)

Select employee_id empid, Hire_date hiredate,

Salary Sal, commission_pct comm

From Employees

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/8F/38/wKiom1jX55-i7xUoAAC-0_pDMWg002.jpg "title=" Qq20170326222330.jpg "width=" 720 "height=" 480 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:720px;height:480px; "alt = "Wkiom1jx55-i7xuoaac-0_pdmwg002.jpg"/>

Conditional Insert First

Insert First

When salary <

into Sal_low values (employee_id, last_name, salary)

When salary between and 10000 then

into Sal_mid values (employee_id, last_name, salary)

Else

into Sal_high values (employee_id, last_name, salary)

Select employee_id, last_name, salary

From Employees

Rotate Insert

To set a sales record from a non-relational database table to a relational format

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8F/38/wKiom1jX6F7jhKb5AAB4z1D8yJo619.jpg "title=" Qq20170326222330.jpg "alt=" Wkiom1jx6f7jhkb5aab4z1d8yjo619.jpg "/>

Insert All

into sales_info values (Employee_id,week_id,sales_mon)

into sales_info values (employee_id,week_id,sales_tue)

into sales_info values (employee_id,week_id,sales_wed)

into sales_info values (Employee_id,week_id,sales_thur)

into sales_info values (employee_id,week_id, Sales_fri)

Select employee_id, week_id, Sales_mon, Sales_tue,

Sales_wed, Sales_thur,sales_fri

From Sales_source_data;

Restriction conditions

• You can only perform multiple table INSERT statements on a table, not on a view or materialized views;

• Multiple table INSERT statements cannot be performed on the remote table;

• Cannot use table set expression;

• Cannot exceed 999 target columns;

• A multi-table INSERT statement cannot be executed in parallel in a RAC environment or when the target table is an index-organized table or a bitmap index on the target table;

• Multi-Table INSERT statements do not support execution plan stability;

• A subquery in a multiple-table INSERT statement cannot use a sequence.

MERGE statement

• Provides the ability to update, insert, and delete data based on conditions

• If the data exists to perform an update, if it does not exist, insert:

– Avoid individual updates

– Improved performance and ease of use

– Very suitable for data warehousing

MERGE statement syntax

Using the merge statement, you can insert, update, or delete rows in a table based on conditions

Merge into table_name Table_alias

using (table|view|sub_query) alias

On (Join condition)

When matched then

Update set

col1 = Col1_val,

col2 = Col2_val

When isn't matched then

Insert (column_list)

Values (column_values);

Merge rows: Example

Inserts or updates a row in the Copy_emp3 table that matches the employees.

Merge into Copy_emp3 C

Using (SELECT * FROM Employees) E

On (c.employee_id = e.employee_id)

When matched then

Update set

C.first_name = E.first_name,

C.last_name = E.last_name,

...

Delete where (e.commission_pct is not null)

When isn't matched then

Insert VALUES (e.employee_id, E.first_name, E.last_name,

E.email, E.phone_number, E.hire_date, e.job_id,

E.salary, e.commission_pct, e.manager_id,

E.DEPARTMENT_ID);

Merge Rows Example

TRUNCATE TABLE Copy_emp3;

SELECT * from Copy_emp3;

Merge into Copy_emp3 C

Using (SELECT * FROM Employees) E

On (c.employee_id = e.employee_id)

When matched then

Update set

C.first_name = E.first_name,

C.last_name = E.last_name,

...

Delete where (e.commission_pct is not null)

When isn't matched then

Insert VALUES (e.employee_id, E.first_name, ...

Tracking changes in data

Flash Back Version Query example

Select salary from Employees3 where employee_id = 107;

Update Employees3 Set salary = salary * 1.30 where employee_id = 107;

Commit

Select salary from Employees3 versions between SCN MinValue and maxvalue where employee_id = 107;

VERSIONS between clause

Select Versions_starttime "Start_date",

Versions_endtime "End_date",

Salary

From Employees

Versions between SCN MinValue

and MaxValue

where last_name = ' Lorentz ';

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1910564

SQL base subquery, multi-table Insert, merge statement, track time data changes (20)