"insert or update" in one step?
Todd
want to insert a certain row if it's not there, or else just update it, without
doing a select first and then branching to either an insert or update.
Thanks! - Todd (gdu...@zdnetmail.edu - change edu to com)
Spencer
database is to use a pl/sql block (either anonymous or
stored will work)
you've got the idea though... select "for update" the row
by key value. if found, update it, if not found, then insert.
HTH
"Todd" <gdu...@zdnetmail.edu> wrote in message
news:3b159f56$0$18...@news.denver1.Level3.net...
Jørn Hansen
/Jørn
"Todd" <gdu...@zdnetmail.edu> wrote in message
news:3b159f56$0$18...@news.denver1.Level3.net...
Martin
Hi
if you're using a PL/SQL-Block than another way is
BEGIN
INSERT
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE
END;
or
BEGIN
UPDATE
IF SQL%ROWCOUNT=0 THEN
INSERT
END IF;
END;
If you are doing more Insert's use the first Block, else the second one.
HTH
Martin
jw
could you post a link to any documentation on that new command ?
Thanks!
"Jørn Hansen" <j...@stibo.dk> schrieb im Newsbeitrag
news:99129403...@radon.stibo.dk...
Patrick Sinke
>
this must be a joke!
Maybe there will be a combined update/delete statement? Like uplete or
delate? ;)
Regards,
Patrick.
Jonathan Lewis
Not necessarily a joke, UPSERT has been a valid
command in one of the major databases (Teradata
I think) for several years, and the question: 'I want to
insert a row, or update it if it exists, what's the
efficient method ?' is a prime candidate for any
Oracle FAQ. (There was yet another thread started
on it in the last few days).
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
Patrick Sinke wrote in message <99131119...@sleeper.capgemini.nl>...
Todd
In article <99129403...@radon.stibo.dk>, j...@stibo.dk says...
Todd
"7.22 REPLACE Syntax
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...)
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...
REPLACE works exactly like INSERT, except that if an old record in the table
has the same value as a new record on a unique index, the old record is deleted
before the new record is inserted. See section 7.21 INSERT Syntax."
In article <991313547.19776.0...@news.demon.co.uk>,
jona...@jlcomp.demon.co.uk says...
Thomas Kyte
(if that email address didn't require changing)
from the Oracle9i new features guide
http://technet.oracle.com/products/oracle9i/content.html
...
To increase the efficiency and reduce time taken to load and refresh critical
data warehouses, Oracle9i provides support for external tables, allowing data
from external systems to be quickly loaded into the database. In addition, a new
data capture facility will allow incremental changes from target sources to be
captured and applied to the data warehouse automatically. Other data load
capabilities provided to increase data load scalability and reduce complexity
include multi table insert and upsert semantics. This new ETL functionality will
also be leveraged by Oracle Warehouse Builder.
......
--
Thomas Kyte (tk...@us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp
Spencer
is a descriptive name for the functionality available as part
of the Teradata MLOAD utility. the control card file has to
contain two separate SQL statements: an insert statement
and an update statement, and then a single step can refer
to an input file, and both of the statements. sweet!
it would be so nice to have this same functionality in the
Oracle SQL*Loader utility. for a datamart/warehouse
using Oracle 7.3, we prepared a file, ran sqlldr, got the
discard file (duplicate key values), and used that as an
input file to a Pro*C program to perform updates.
i wonder if 9i is going to include this functionality in the
SQL*Loader. now that would be Sweet...!
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:991313547.19776.0...@news.demon.co.uk...
Spencer
you are right about which example to use in which situation.
if you performing nearly all inserts, then perform the insert first.
if you are mostly updating, then perform the update first.
what if you have a fairly good mix ? performing an insert
that raises the ORA-00001 exception actually generates
redo. if any constraints have been deferred, an exception
may not be raised until a commit is performed.
if you are concerned with avoiding "hung" sessions due to
a locked row, then use a cursor FOR UPDATE NOWAIT,
which will immediately raise a ORA-00054 exception if a
row is locked by another session. a plain old UPDATE
statement will wait, and wait, and wait... for most of our
applications, it is better to catch the -54, and let the app
handle retry logic. the app already has to deal with a lot
of other oracle exceptions. sometimes, we catch the -54
exception, and translate this to a function return code (or
an OUT argument)
for some tables, the app doesn't care what values are in
the columns, we app wants to replace them. a lot of times,
they are updating the row with the exact values that are
already in the row, and that generates rollback and redo.
so i like to have an opportunity to compare the values in
the columns with the new values, and determine whether
an update is actually required or not.
here is an quick example of the approach i prefer to use
in some cases. it looks like more code, but in testing, it
actually provides better performance (primarily due to a
reduction in the amount of rollback and redo generated.)
PROCEDURE upsert_mytable
(as_key1 VARCHAR2
,as_col2 VARCHAR2
,as_commit VARCHAR2
)
IS
CURSOR lcsr_lockrow IS
SELECT t.ROWID, t.*
FROM MYTABLE t
WHERE t.KEY = as_key1
FOR UPDATE NOWAIT;
BEGIN
OPEN lcsr_lockrow;
FETCH lcsr_locrow INTO lrec;
IF lcsr_lockrow%NOTFOUND THEN
INSERT INTO MYTABLE
(KEY1, COL2)
VALUES
(as_key1, as_col2);
ELSE
IF lrec.COL2 = as_col2 THEN
NULL;
ELSE
UPDATE MYTABLE t
SET t.COL2 = as_col2
WHERE t.ROWID = lrec.ROWID;
END IF;
END IF;
CLOSE lcsr_lockrow;
-- optionally perform a commit
IF as_commit = 'Y' THEN
COMMIT;
END IF;
EXCEPTION
-- optionally handle ORA-00054
WHEN busy_nowait_excep THEN
RAISE;
WHEN OTHERS THEN
RAISE;
END upsert_mytable;
"Martin" <m.su...@freenet.de> wrote in message
news:d2a2bf74.01053...@posting.google.com...
Mark Townsend
spen...@swbell.net wrote on 6/2/01 12:54 AM:
> actually, UPSERT is not really a Teradata statement, but it
> is a descriptive name for the functionality available as part
> of the Teradata MLOAD utility. the control card file has to
> contain two separate SQL statements: an insert statement
> and an update statement, and then a single step can refer
> to an input file, and both of the statements. sweet!
>
> it would be so nice to have this same functionality in the
> Oracle SQL*Loader utility. for a datamart/warehouse
> using Oracle 7.3, we prepared a file, ran sqlldr, got the
> discard file (duplicate key values), and used that as an
> input file to a Pro*C program to perform updates.
>
> i wonder if 9i is going to include this functionality in the
> SQL*Loader. now that would be Sweet...!
Sort of - instead of loading your file through SQL*Loader, in Oracle9i you
just attach the input file as an external table, and then MERGE from this
table into your target table.
See
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/
a90237/transfor.htm#13134 for a description of External Tables, and a few
pages down for MERGE examples