8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

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:

Hope this helps. Regards Tim...

Back to the Top.