Changing Data in a Table
UPDATE Statement Syntax
- Modify existing values in a table with the UPDATE statement
UPDAE table SET column = value [,column = value,...] [WHERE condition];
- Update more than one row at a time(if required)
Updating Rows in a Table
- Values for a specific row or rows are modified if you specify th WHERE clause:
UPDATE employees SET department_id = 50 WHERE employee_id = 113;
- Values for all the rows in the table are modified if you omit the WHERE clause:
UPDATE copy_emp SET department_id = 110;
- Sepecify SET column_name = NULL to update a column vaue to NULL.
Updating Two Columns with a Subquery
Update employee 113`s job and salary to match those of employee 205.
UPDATE employees SET job_id = ( SELECT job_id FROM employees WHERE employee_id = 205 ), alary = ( SELECT salary FROM employees WHERE employee_id = 205 ) WHERE employee_id = 113;
Updating Rows Based on Anohter Table
Use the subqueries in the UPDATE statements to update row values in a table based on values from another table:
UPDATE emp SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100) WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200)
Removig a ROw from a Table
DELETE Statement Syntax
You can remove existing rows from a table by using the DELETE statement:
DELETE [FROM] table [WHERE condition]
Deleting Rows from a Table
Specific rows are deleted if you specify the WHERE clause:
DELETE FROM departments WHERE departement_name = 'Finance';
All rows in the table are deleted if you omit the WHEE clause:
DELETE FROM emp;
Deleting Rows Based on Another Table
Use the subqueries in the DELETE statements to remove rows from a table based on values from another table:
DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Pubblic%');
TRUNCATE Statement
- Removes all rows from a table,leaving the table empty and the table structure intact.
- Is a data definition language(DDL) statement rather than a DML statement;cannot easily be undone
- Syntax:
TRUNCATE TABLE table_name
- Example:
TRUNCATE TABLE emp
Database Transactions
A database transaction consists of one of the following:
- DML statements that consitute one consistent change to the data.
- One DDL statement.
- One data control language(DCL) statement.
Database Transactions:Start and End
- Begin when the first DML SQL statement is executed.
- End with one of the following events:
- A COMMIT or ROLLBACK statement is issued.
- A DDL or DCL statement executes(automatic commit).一个事务开始的时候，上一个事务会自动提交;
- The uesr exits SQL Developer or SQL*Plus.
- The system crashes.
Advantags of COMMIT and ROLLBACK Statements
With COMMIT and ROLLBACK statements,you can :
- Ensure data consistency.
- Preview data changes before making changes permanent.
- Group logically-related operations.
Explicit Transaction Control Statements
Transaction Demo
SQL> SELECT * FROM dept; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected. SQL> DELETE FROM dept WHERE department_id = 270; 1 row deleted. SQL> SAVEPOINT a; Savepoint created. SQL> INSERT INTO dept VALUES(280,'BBBBB',NULL,NULL); 1 row created. SQL> SAVEPOINT b; Savepoint created. SQL> UPDATE dept SET department_name='BBCCDD' WHERE department_id=280; 1 row updated. SQL> SELECT * FROM dept; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 280 BBCCDD 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 110 Accounting 205 1700 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 220 NOC 1700 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 27 rows selected. SQL> ROLLBACK TO a; Rollback complete. SQL> SELECT * FROM dept; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 26 rows selected. SQL> ROLLBACK; Rollback complete. SQL> SELECT * FROM dept; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected.