 |
|
EnterpriseDB: UPDATE COMMAND
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;
UPDATE 8
edb=#
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;
UPDATE 33
edb=#
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):
UPDATE emp
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:
INFO: User enterprisedb updated employee(s) on 2006-11-09
UPDATE 17
edb=#
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;
UPDATE 8
edb=#
That
just about covers it for UPDATE. We are going to cover the
DELETE command now.
DELETE
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:
DELETE FROM <table>
[WHERE <limiting criteria>];
To
delete all of the rows in a table:
DELETE FROM left_table;
edb=# DELETE FROM left_table;
DELETE 33
edb=#
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;
DELETE 1
edb=#
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.
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:
TRUNCATE TABLE <table>;
To
unconditionally remove all records from a table:
TRUNCATE TABLE right_table;
edb=# TRUNCATE TABLE right_table;
TRUNCATE TABLE
edb=# rollback;
ROLLBACK
edb=# select count(*) from right_table;
count
-------
0
(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.
