8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL for Beginners (Part 9) : The UPDATE Statement
This is the ninth part of a series of articles showing the basics of SQL. In this article we take a look at some of the variations on the UPDATE
statement.
Related articles.
- SQL for Beginners (Part 9) : The UPDATE Statement
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
- Updates Based on Queries
- DML RETURNING INTO Clause
Setup
You can perform all these queries online for free using SQL Fiddle.
The examples in this article require the following tables to be present.
--DROP TABLE employees PURGE; --DROP TABLE departments PURGE; CREATE TABLE departments ( department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, department_name VARCHAR2(14), location VARCHAR2(13) ); INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO departments VALUES (20,'RESEARCH','DALLAS'); INSERT INTO departments VALUES (30,'SALES','CHICAGO'); INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON'); COMMIT; CREATE TABLE employees ( employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY, employee_name VARCHAR2(10), job VARCHAR2(9), manager_id NUMBER(4), hiredate DATE, salary NUMBER(7,2), commission NUMBER(7,2), department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id) ); INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.
COMMIT and ROLLBACK
All Data Manipulation Language (DML) changes are done as part of a transaction. They are not permanent until they are confirmed using the COMMIT
statement. Once committed, the only way you can revert a change is to issue a new DML statement to alter the data. Several statements can be grouped together to form a single transaction.
Data Definition Language (DDL) commands perform an implicit commit, which also confirms all outstanding DML changes in the current session.
If you decide you don't want to keep some uncommitted changes, you can throw them away using using the ROLLBACK
statement. Many of the examples in this article will issue ROLLBACK
statements after the test, to revert the data to its original state.
Some tools and programming languages default to auto-commit, so they automatically issue a COMMIT
statement after every DML statement they process. Don't let this fool you into thinking this is default behaviour. It is not.
Basic UPDATE
The UPDATE
statement is used to alter the column values in an existing row. Without a WHERE
clause, all rows in the table are updated by a single statement.
The following query lists all the SALARY values in the EMPLOYEES table.
SELECT e.salary FROM employees e ORDER BY e.salary; SALARY ---------- 800 950 1100 1250 1250 1300 1500 1600 2450 2850 2975 3000 3000 5000 14 rows selected. SQL>
Let's update them all by adding 1 to them.
UPDATE employees SET salary = salary + 1; 14 rows updated. SQL> SELECT e.salary FROM employees e ORDER BY e.salary; SALARY ---------- 801 951 1101 1251 1251 1301 1501 1601 2451 2851 2976 3001 3001 5001 14 rows selected. SQL>
The WHERE
clause is used to limit the scope of the statement. We might want to update a small number of rows, or even a single row by using a filter on the primary key column(s) of the table.
UPDATE employees SET salary = salary - 1 WHERE salary >= 2000; 6 rows updated. SQL> SELECT e.salary FROM employees e ORDER BY e.salary; SALARY ---------- 801 951 1101 1251 1251 1301 1501 1601 2450 2850 2975 3000 3000 5000 14 rows selected. SQL> -- Revert all the changes. ROLLBACK;
Multiple column values can be amended in a single UPDATE
statement.
UPDATE employees SET salary = 9999, commission = 1000, manager_id = 7566 WHERE employee_id = 7369; 1 row updated. SQL> ROLLBACK;
UPDATE via View
It's possible to update the base table associated with a view. There are some restrictions associated with this, but they are a little out of scope for a beginner level article. In the following example, we create a simple view on the EMPLOYEES table, then update via it.
CREATE OR REPLACE VIEW employees_v AS SELECT * FROM employees; UPDATE employees_v SET salary = 1000 WHERE employee_id = 7369; 1 row updated. SQL> ROLLBACK;
You won't see it very often, but you can also update via inline views. This can be used to control the number of rows updated, rather than using a filter in the WHERE
clause of the UPDATE
statement itself.
UPDATE (SELECT employee_id, salary FROM employees WHERE department_id = 20) SET salary = 4000; 5 rows updated. SQL> ROLLBACK;
Scalar Subqueries
The updated value can come from a scalar subquery. The following UPDATE
sets the SALARY of employee 7369, to that of the highest paid employee.
UPDATE employees SET salary = (SELECT MAX(salary) FROM employees) WHERE employee_id = 7369; 1 row updated. SQL> ROLLBACK;
0 Rows Updated
An update of zero rows is a valid update, and does not result in an error. This can prove rather confusing to beginners.
UPDATE employees SET salary = 10000 WHERE employee_id = 9999; 0 rows updated. SQL>
As a result, you can't test for failure to update rows using the NO_DATA_FOUND
exception in PL/SQL, as it doesn't get raised.
SET SERVEROUTPUT ON BEGIN UPDATE employees SET salary = 10000 WHERE employee_id = 9999; DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised'); END; / NO_DATA_FOUND Not Raised PL/SQL procedure successfully completed. SQL>
Instead, you must manually test for the number of rows updated using SQL%ROWCOUNT
.
SET SERVEROUTPUT ON BEGIN UPDATE employees SET salary = 10000 WHERE employee_id = 9999; IF SQL%ROWCOUNT = 0 THEN -- Manually raise the NO_DATA_FOUND exception. RAISE NO_DATA_FOUND; END IF; DBMS_OUTPUT.put_line('NO_DATA_FOUND Not Raised'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('NO_DATA_FOUND Raised'); END; / NO_DATA_FOUND Raised PL/SQL procedure successfully completed. SQL>
For more information see:
- SQL for Beginners (Part 9) : The UPDATE Statement
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
- Database SQL Language Reference : UPDATE
- Updates Based on Queries
- DML RETURNING INTO Clause
Hope this helps. Regards Tim...