Oracle SQL - Data Change Command UPDATE Command

Introduction

You can change column values of existing rows with the UPDATE command.

The UPDATE command has three main components:

UPDATE ...: The table you want to update
SET ...: The change you want to apply
WHERE ...: The rows to which you want to apply the change

If you omit the optional WHERE clause, the change is applied to all rows of the table.

The UPDATE command operates at the table level, so you need the WHERE clause as the relational restriction operator to limit its scope.

The SET clause offers two alternatives:

You can specify a comma-separated list of single column changes.
You can use a subquery.

The following code shows how to use UPDATE Command to change one row.

In the following example, the employee with empno of 7011 has their job, msal, comm, and deptno updated with new values.

In the case of msal, the new value is based on the current (pre-UPDATE) value of the column.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w ww  .j  a  v  a2  s  .c  om

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> update emp
  2  set    job    = 'SALESREP'
  3  ,      msal   = msal - 500
  4  ,      comm   = 0
  5  ,      deptno = 30
  6  where  empno  = 7011;

1 row updated.

SQL>
SQL> select * from emp;

    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE      |      MSAL
--------- | -------- | ----- | -------- | --------- | ---------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07001.00 | SMITH    | N     | TRAINER  |  07902.00 | 17-12-1975 |  01800.00
 [N/A]    |  00020.00

 07002.00 | ALLEN    | JAM   | SALESREP |  07006.00 | 20-05-1971 |  01600.00
 00300.00 |  00030.00

 07003.00 | WARD     | TF    | SALESREP |  07006.00 | 02-03-1972 |  01250.00
 00500.00 |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE      |      MSAL
--------- | -------- | ----- | -------- | --------- | ---------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07004.00 | JACK     | JM    | MANAGER  |  07009.00 | 02-04-1977 |  02975.00
 [N/A]    |  00020.00

 07005.00 | BROWN    | P     | SALESREP |  07006.00 | 28-09-1976 |  01250.00
 01400.00 |  00030.00

 07006.00 | BLAKE    | R     | MANAGER  |  07009.00 | 01-11-1973 |  02850.00
 [N/A]    |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE      |      MSAL
--------- | -------- | ----- | -------- | --------- | ---------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07007.00 | CLARK    | AB    | MANAGER  |  07009.00 | 09-06-1975 |  02450.00
 [N/A]    |  00010.00

 07008.00 | SCOTT    | DEF   | TRAINER  |  07004.00 | 26-11-1979 |  03000.00
 [N/A]    |  00020.00

 07009.00 | KING     | CC    | DIRECTOR |  [N/A]    | 17-10-1972 |  05000.00
 [N/A]    |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE      |      MSAL
--------- | -------- | ----- | -------- | --------- | ---------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07010.00 | BREAD    | JJ    | SALESREP |  07006.00 | 28-09-1978 |  01500.00
 00000.00 |  00030.00

 07011.00 | ADAMS    | AA    | SALESREP |  07008.00 | 30-12-1976 |  00600.00
 00000.00 |  00030.00

 07012.00 | JONES    | R     | ADMIN    |  07006.00 | 03-10-1979 |  08000.00
 [N/A]    |  00030.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE      |      MSAL
--------- | -------- | ----- | -------- | --------- | ---------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07902.00 | FORD     | MG    | TRAINER  |  07004.00 | 13-02-1979 |  03000.00
 [N/A]    |  00020.00

 07934.00 | MARY     | ABC   | ADMIN    |  07007.00 | 23-01-1972 |  01300.00
 [N/A]    |  00010.00


14 rows selected.

SQL>

Oracle SQL - Data Change Command UPDATE Command

Introduction

Demo

Related Topics