Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

Basics of the Insert, Update, and Delete Statements in Oracle 12c

To add rows to your Oracle 12c database, you use the INSERT statement. An INSERT statement acts on one table at a time. The INSERT statement has three clauses, of which one is optional:

  • INSERT clause

  • Column clause (optional)

  • VALUES clause

Here’s how you would insert a new row into the jobs table:

  1. For the INSERT clause, type

    <INSERT INTO jobs>

    and press Enter.

  2. For the columns clause, type

    <(job_id, job_title)>

    and press Enter.

  3. For the VALUES clause, type

    <VALUES ('TRN_MGR','TRAINING MANAGER');>

    and press Enter.

    You see

    SQL> INSERT INTO jobs
     2 (job_id, job_title)
     3 VALUES ('TRN_MGR','TRAINING MANAGER');
    1 row created.

    After you add one row to your table, the results appear as follows:

    SQL> SELECT *
     2 FROM jobs
     3 WHERE job_id = 'TRN_MGR';
    JOB_ID   JOB_TITLE              MIN_SALARY MAX_SALARY
    ---------- ----------------------------------- ---------- ----------
    TRN_MGR  TRAINING MANAGER

    The salary columns are empty.

Single quotes (‘ @@‘) must be used around character fields. Anything that contains characters (such as a, b, or c) needs to have single quotes around it if you’re talking about data. Numeric fields can be left without quotes.

How to change data with the UPDATE statement in Oracle 12c

If you have data which you want to modify, use the UPDATE statement. The UPDATE statement acts on columns. Here are the clauses of the UDPATE statement:

  • UPDATE clause

  • SET clause

  • WHERE clause

The first two clauses are required. Technically, the last clause is optional albeit highly recommended.

If you “forgot” the salary information in our INSERT statement, here’s how to fix it with an UPDATE statement:

  1. Type

    <UPDATE jobs>

    and press Enter.

  2. Type

    <SET min_salary = 10000, max_salary = 20000>

    and press Enter.

  3. Type

    <WHERE job_id = 'TRN_MGR';>

    and press Enter.

    You see

    SQL> UPDATE jobs
     2 SET min_salary = 10000, max_salary = 20000
     3 WHERE job_id = 'TRN_MGR';
    1 row updated.

    And the results are

    SQL> SELECT *
     2 FROM jobs
     3 WHERE job_id = 'TRN_MGR';
    JOB_ID   JOB_TITLE              MIN_SALARY MAX_SALARY
    ---------- ----------------------------------- ---------- ----------
    TRN_MGR  TRAINING MANAGER             10000   20000

Always consider using a WHERE clause with an UPDATE statement, or else you update all the rows.

Use your WHERE clause with your UDPATE statement to form a SELECT statement. That way, you can verify that your WHERE clause is acting on the correct data before you run your update. (This would also apply to a DELETE statement.)

If you like what you see, you have to make your changes permanent. Type <COMMIT;> and then press Enter.

You see:

SQL> commit;
Commit complete.

And, your changes cannot be easily undone.

How to remove data with the DELETE statement in Oracle 12c

The last DML-type statement to talk about is the DELETE statement. The DELETE statement allows you to remove rows from tables. DELETE acts on one table at a time. You should also carefully consider using a WHERE clause with your DELETE statement, or else all your rows will be removed.

The DELETE statement has two clauses:

  • DELETE clause

  • WHERE clause

Here’s how to remove the last rows you just added to the database for the TRN_MGR job_id:

  1. Type

    <DELETE FROM jobs>

    and press Enter.

  2. Type

    <WHERE job_id = 'TRN_MGR';>

    and press Enter.

    You see

    SQL> DELETE FROM jobs
     2 WHERE job_id = 'TRN_MGR';
    1 row deleted.

    And the results appear as follows:

    SQL> SELECT *
     2 FROM jobs
     3 WHERE job_id = 'TRN_MGR';
    no rows selected

There are no longer any rows in the table for the job_id TRN_MGR. Additionally, all the columns were removed. To remove just one of the values, use an UPDATE statement and set the column to empty (null). DELETE always acts on all columns; it removes rows.

Oops! You did not mean to DELETE the TRN_MGR row! Luckily, you did not COMMIT our change yet. You can easily undo this change with a ROLLBACK statement:

SQL> ROLLBACK;
Rollback complete.
SQL> SELECT *
 2 FROM jobs
 3 WHERE job_id = 'TRN_MGR';
JOB_ID   JOB_TITLE              MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
TRN_MGR  TRAINING MANAGER             10000   20000

As long as you have not issued a COMMIT in your session, you can rollback any changes to the last COMMIT within your session. Also, until you commit your data, no one else in the database can see it.

Leaving data un-committed for long periods of time can cause locking problems in your database. Data that has been changed and not committed holds a lock on the row(s) in question. You should commit your changes as soon as possible.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.