BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation









Oracle Tips by Burleson Consulting

Update allows us to modify existing data.  Update in EnterpriseDB has a couple of extensions that you may or may not need at some point.  I will stick with the more common syntax.

The UPDATE command follows the basic format of:

UPDATE <table>
  SET <column> = <expression|value|query>
  [WHERE <limiting criteria>];

We can update all of the values in our left_table that are greater than 1 to .5:

UPDATE left_table
  SET amount = .5
  WHERE amount > 1;

edb=# UPDATE left_table
edb-#   SET amount = .5
edb-#   WHERE amount > 1;

If we did not include the WHERE clause, all of the records in our table would have been set to .5.

We could also set values to an expression:

UPDATE left_table
  SET amount = amount + .03; 

edb-#   SET amount = amount + .03;

In this example, we added .03 to every amount in the left_table.

We only have a single column in the left_table.  If we wanted to update multiple columns, we would use a comma (,) to separate the columns.  Let's update the emp table and set sal = sal * 1.03 (giving the employee a 3% raise) and let's set comm = comm. * 1.05 (giving them a 5% raise on their commissions):


  SET sal = sal * 1.03,
      comm = comm * 1.05;

I'm not going to put the entire output here but you should see something like this:

edb=# UPDATE emp
edb-#   SET sal = sal * 1.03,
edb-#       comm = comm * 1.05;

INFO:  Updating employee 7369
INFO:  ..Old salary: 800.00
INFO:  ..New salary: 824.00
INFO:  ..Raise     : 24.00
INFO:  Updating employee 7499
INFO:  ..Old salary: 1600.00
INFO:  ..New salary: 1648.00
INFO:  ..Raise     : 48.00
INFO:  Updating employee 7521
INFO:  ..Old salary: 1250.00
INFO:  ..New salary: 1287.50
INFO:  ..Raise     : 37.50
INFO:  User enterprisedb updated employee(s) on 2006-11-09


And finally, you can update a column via a select statement.  Let's update the left_table and set amount equal to the sal of the employee 'JONES' only where the amount is greater than .5: 

UPDATE left_table
  SET amount = (
         SELECT sal
           FROM emp
           WHERE ename = 'JONES' )
  WHERE amount > .5; 

edb=# UPDATE left_table
edb-#   SET amount = (
edb(#          SELECT sal
edb(#            FROM emp
edb(#            WHERE ename = 'JONES' )
edb-#   WHERE amount > .5;


That just about covers it for UPDATE.  We are going to cover the DELETE command now.


The DELETE command allows you to selectively remove rows from a table.  Delete is the second easiest (and of course, the second most dangerous) of all the DML commands.

Delete follows the basic format of:

  [WHERE <limiting criteria>];

To delete all of the rows in a table:

DELETE FROM left_table;

edb=# DELETE FROM left_table;

To delete all of the rows greater than .20:

DELETE FROM right_table
  WHERE amount > .20;

edb=# DELETE FROM right_table
edb-#   WHERE amount > .20;


That covers DELETE.  Of course, like all of the DML commands, the WHERE clause can contain sub-queries.  We've used those quite a bit above so I will not do so here but I did want to remind you of that functionality.

The last DML command that we will cover is TRUNCATE. 


I said above that the DELETE command was the second easiest and the second most dangerous.  TRUNCATE is number one in both categories.  While DELETE removes data conditionally, TRUNCATE removes data unconditionally.  When you want to remove all data from a table, TRUNCATE is faster than a DELETE.   However, you can ROLLBACK from a DELETE, you cannot ROLLBACK from a TRUNCATE.

TRUNCATE follows the basic format of:


To unconditionally remove all records from a table:

TRUNCATE TABLE right_table;

edb=# TRUNCATE TABLE right_table;
edb=# rollback;


edb=# select count(*) from right_table;


(1 row)

That's it for DML commands.  Now we will cover a special command that can increase performance for your applications in special circumstances.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter