Oracle SQL - Using a subquery to insert rows with the INSERT

  1. HOME
  2. Oracle SQL
  3. Data Change Command
  4. INSERT Command

Introduction

The following example shows how you can use a subquery to insert rows with the INSERT command.

It uses the LEAST function to avoid constraint violations.

The first argument (9999) ensures that the upper limit will never become greater than 9999.

Demo

SQL>
SQL> drop table salgrades;

Table dropped.-- from  ww w. j av a  2 s .  c o m

SQL> create table salgrades(
  2  grade      NUMBER(2)   primary key,
  3  lowerlimit NUMBER(6,2) not null check (lowerlimit >= 0),
  4  upperlimit NUMBER(6,2) not null,
  5  bonus      NUMBER(6,2) not null);
SQL>
SQL> insert into salgrades values (1,  700,1200,   0);
SQL> insert into salgrades values (2, 1201,1400,  50);
SQL> insert into salgrades values (3, 1401,2000, 100);
SQL> insert into salgrades values (4, 2001,3000, 200);
SQL> insert into salgrades values (5, 3001,9999, 500);
SQL>
SQL> insert into salgrades
  2  select grade + 5
  3  ,      lowerlimit + 2300
  4  ,      least(9999, upperlimit + 2300)
  5  ,      500
  6  from   salgrades;

5 rows created.

SQL>
SQL> select * from salgrades;

    GRADE | LOWERLIMIT | UPPERLIMIT |     BONUS
--------- | ---------- | ---------- | ---------
 00001.00 |   00700.00 |   01200.00 |  00000.00
 00002.00 |   01201.00 |   01400.00 |  00050.00
 00003.00 |   01401.00 |   02000.00 |  00100.00
 00004.00 |   02001.00 |   03000.00 |  00200.00
 00005.00 |   03001.00 |   09999.00 |  00500.00
 00006.00 |   03000.00 |   03500.00 |  00500.00
 00007.00 |   03501.00 |   03700.00 |  00500.00
 00008.00 |   03701.00 |   04300.00 |  00500.00
 00009.00 |   04301.00 |   05300.00 |  00500.00
    1     |   05301.00 |   09999.00 |  00500.00

10 rows selected.

SQL>

Related Topic