Re: simultaneous access of two tables

In article <3qd4rh$si5_at_cs5.cs.ait.ac.th> b94327_at_cs.ait.ac.th (Miss Ma Jessica S Cocjin) writes:

> hello there...
> can anybody help me?
>
> i have two tables (t1 and t2), i want to update t1 with the values
> of t2, take note: not all records in t1 are in t2.
>
> have tried using declare... open.. fetch but it seems it does not work.
> any suggestions are greatly appreciated by me...

Performance issues aside, this is by far the easiest way of achieving your task:

	UPDATE t1
	SET    col1 = (
		SELECT col1
		FROM   t2
		WHERE  ....
	)
	WHERE EXISTS (
		SELECT null
		FROM   t2
		WHERE  ....
	);

The side effect is that this approach requires a full-table scan of t1. This may not be that bad if you only have a couple hundred records in t1 or you're updating a large percentage of the records in t1, but something to avoid if you have a couple million records in t1 and you only want to update a small subset of those records.

Alternately, if t1 is large and you only wants to update a few rows, you may want try this:

	UPDATE t1
	SET    col1 = (
		SELECT col1
		FROM   t2
		WHERE  ....
	)
	WHERE rowid IN (
		SELECT DISTINT t1.rowid
		FROM   t1, t2
		WHERE  ....
	);

A variation of this approach would be to use a cursor fetch:

	DECLARE CURSOR a
		SELECT DISTINCT t1.rowid, t2.col1
		FROM   t1, t2
		WHERE  ....;

	UPDATE  t1
	SET     col1 = a.col1
	WHERE   rowid = a.rowid;

You may also want to seriously consider redesigning your data model, i.e. merging the two tables into one.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel