lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle Data Manipulation Language


Data in the tables is maintained using Data Manipulation Language (DML).

DML consists of the following statements (although SELECT is sometimes included in this list too).
  • INSERT
  • UPDATE
  • DELETE

Insert

The INSERT statement takes two forms:
INSERT INTO table_name (column_list) VALUES (value_list);

INSERT INTO table_name VALUES (value_list);
Let's use the first option to add Spain to the COUNTRY table (with some deliberate mistakes).
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(28, 'pain', 195364, 5, '1-jul-2009', 'Norwegian Krone');

COMMIT;

SELECT *
FROM country
WHERE ctry_id = 28;
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
28 pain 195364 5 1-jul-2009 Norwegian Krone

The column_list is optional so we could have used this SQL instead:
INSERT INTO country
  VALUES (28, 'pain', 195364, 5, '1-jul-2009', 'Norwegian Krone');

COMMIT;
It is good practice, however, to name the columns in the INSERT statement. It makes the SQL easier to follow and applications more resilient to change (if the column order is changed or new columns are added to the table, for example).

Naming the columns also allows us to omit optional values during the insert.
INSERT INTO country (ctry_id, ctry_name, ctry_area)
  VALUES(28, 'pain', 195364);

COMMIT;
In this case, POP, POP_UPD_ON and CURRENCY are set to null because no values have been provided for them.

Update

The UPDATE statement looks like this:
UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE conditions;
We use this UPDATE statement to fix the mistakes we made when inserting the row.
UPDATE country
SET ctry_name = 'Spain',
    ctry_pop = 46661950,
    currency = 'Euro'
WHERE ctry_id = 28;

-- 1 row updated

COMMIT;

SELECT *
FROM country
WHERE ctry_id = 28;
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
28 Spain 195364 46661950 30-nov-08 Euro

Several rows can be changed with a single UPDATE statement depending on the selection criteria in our WHERE clause. Here we increase the population by three percent and set the updated date to the 1st June 2009, for Spain, Germany and Greece.
UPDATE country
SET ctry_pop = trunc(ctry_pop * 1.03),
    pop_upd_on = '1-jun-09'
WHERE ctry_name IN ('Spain', 'Germany', 'Greece');

-- 3 rows updated

COMMIT;

SELECT *
FROM country
WHERE ctry_name IN ('Spain', 'Germany', 'Greece');
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Germany 137847 84507380 01-JUN-09 Euro
28 Spain 195364 48061808 01-JUN-09 Euro
73 Greece 50949 11595003 01-JUN-09 Euro

Delete

To delete rows from a table, use the DELETE statement.
DELETE FROM table_name
WHERE conditions;
This example removes any small countries.
DELETE FROM country
WHERE ctry_area < 100000;

-- 4 rows deleted

COMMIT;

SELECT *
FROM country;
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Germany 137882 82046000 30-nov-08 Euro
53 Australia 2969907 21884000 4-sep-09 Australian Dollar
123 New Zealand 104428 4320300 4-sep-09 New Zealand Dollar
149 Gabon 103347 1475000 null CFA franc
149 Spain 195364 48061808 01-JUN-09 Euro

Ghana, Greece, Georgia and Gambia have been deleted.

Transactions

You will have noticed that a COMMIT statement followed each of the DML statements above.

A COMMIT makes permanent all DML changes made during a transaction. A ROLLBACK statement, on the other hand, undoes all DML changes made during a transaction.

A transaction is a logical unit of work that comprises one or more SQL statements. A transaction begins when the first SQL statement is executed and ends with a COMMIT or ROLLBACK, at which point ALL changes are made permanent or NONE of them are.

Let's take a look at transactions in action by mistakenly updating the currency for Australia to 'New Zealand Dollar' and removing the New Zealand row altogether.
UPDATE country
SET currency = 'New Zealand Dollar'
WHERE ctry_id = 53;

DELETE FROM country
WHERE ctry_name = 'New Zealand';

-- 1 row updated

SELECT ctry_name, currency
FROM country
WHERE ctry_name IN ('Australia', 'New Zealand');
CTRY_NAME CURRENCY
Australia New Zealand Dollar

The user who made the updates to the database will always see these changes immediately. However, other users of the database must wait until the transaction is committed before they can see the new values.

However, at this point, we realize our mistake and rollback the transaction instead.
ROLLBACK;

SELECT ctry_name, currency
FROM country
WHERE ctry_id IN ('Australia', 'New Zealand');
CTRY_NAME CURRENCY
Australia Australian Dollar
New Zealand New Zealand Dollar

Our changes have been undone and the original data is restored.

Warning!

It may seem obvious, but always make sure you specify the correct condition in the WHERE clause when updating data. If we had mistakenly typed this UPDATE command instead of the one above:
UPDATE country
SET ctry_name = 'Spain',
    ctry_pop = 46661950,
    currency = 'Euro';

-- 8 rows updated
This would be the result:
SELECT *
FROM country;
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Spain 137882 46661950 30-nov-08 Euro
48 Spain 92100 46661950 null Cedi
53 Spain 2969907 46661950 4-sep-09 Australian Dollar
73 Spain 50949 46661950 1-Jan-09 Euro
122 Spain 26900 46661950 1-Jan-09 Lari
123 Spain 104428 46661950 4-sep-09 New Zealand Dollar
147 Spain 4361 46661950 null Dalasi
149 Spain 103347 46661950 null CFA franc
28 Spain 195364 46661950 1-jul-2009 Euro

All CTRY_NAME and all CTRY_POP values have been updated because we didn't include any selection criteria to identify the correct row to update.

You can imagine what would happen if we accidentally did this:
DELETE FROM country;

-- 8 rows deleted
The 8 rows deleted message provides a clue (imagine seeing 76345663 rows deleted on your production database!).
SELECT *
FROM country;

-- 0 rows selected
Fortunately, we have not made the changes permanent by issuing a COMMIT statement. We can still rollback the transaction.
ROLLBACK;

SELECT *
FROM country;
The correct data has been restored.

CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Germany 137882 82046000 30-nov-08 Euro
53 Australia 2969907 21884000 4-sep-09 Australian Dollar
123 New Zealand 104428 4320300 4-sep-09 New Zealand Dollar
149 Gabon 103347 1475000 null CFA franc
149 Spain 195364 48061808 01-JUN-09 Euro
»