DBA Hub

📋Steps in this guide1/6

SQL for Beginners (Part 9) : The UPDATE Statement

In this article we take a look at some of the variations on the UPDATE statement.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
--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;
2

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 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 statement. Many of the examples in this article will issue 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 statement after every DML statement they process. Don't let this fool you into thinking this is default behaviour. It is not.
3

Basic UPDATE

The statement is used to alter the column values in an existing row. Without a clause, all rows in the table are updated by a single statement. The following query lists all the SALARY values in the EMPLOYEES table. Let's update them all by adding 1 to them. The 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. Multiple column values can be amended in a single statement.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
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>

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>

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;

UPDATE employees
SET
salary     = 9999,
       commission = 1000,
       manager_id = 7566
WHERE  employee_id = 7369;

1 row updated.

SQL>

ROLLBACK;
4

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. 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 clause of the statement itself.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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;

UPDATE
(SELECT employee_id, salary
        FROM   employees
        WHERE  department_id = 20)
SET    salary = 4000;

5 rows updated.

SQL>

ROLLBACK;
5

Scalar Subqueries

The updated value can come from a scalar subquery. The following sets the SALARY of employee 7369, to that of the highest paid employee.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
UPDATE employees
SET    salary = (SELECT MAX(salary) FROM employees)
WHERE  employee_id = 7369;
1 row updated.
SQL>

ROLLBACK;
6

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. As a result, you can't test for failure to update rows using the exception in PL/SQL, as it doesn't get raised. Instead, you must manually test for the number of rows updated using . 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
UPDATE employees
SET    salary = 10000
WHERE  employee_id = 9999;

0 rows updated.

SQL>

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!