| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL Update
In article <394D8B51.60B06806_at_blarg.net>,
Dinendra <dinendra_at_blarg.net> wrote:
> Thomas,
> Couple of questions on your solution:
> 1)In your second solution why do you have a clause "where exists"?
>
> update name set columnName = ( select value from lookup where
> lookup.keyname = name.keyname and otherColumn = :other_value )
> where exists
> ( select value from lookup where lookup.keyname = name.keyname and
> otherColumn = :other_value )
>
> Since you already have the condition "lookup.keyname = name.keyname
and
> otherColumn = :other_value " I would think that is sufficient for
finding
> matching rows.
>
if there are any rows in NAME that do not have a match in LOOKUP, the co-related subquery will have the effect of setting the columnName column to NULL when it cannot find a match. The where clause makes it so we update only rows that have a match instead of EVERY row.
this is why i like to update the JOIN:
> > scott_at_ORA734.WORLD> update
> > 2 ( select columnName, value
> > 3 from name, lookup
> > 4 where name.keyname = lookup.keyname
> > 5 and lookup.otherColumn = :other_value )
> > 6 set columnName = value
> > 7 /
not use a correlated subquery with the WHERE EXISTS.
> 2)Is there a way to update a column in a table by joining it with two
> other tables?
> For example:
>
> update table1 set table1.xz = (select table3.xz from table2,table3
> where
> table1.xx = table2.xx and table2.xy =table3.xy)
>
> Assuming:
> table1 has rows - xx, xz
> table2 has rows - xx, xy
> table3 has rows -xy, xz
>
> When I tried the above statement from a PL/SQL procedure it seems to
> update all the rows in table1 regardless of the join condition. I am
> probably missing something here.
>
that is not an example of updating a join. that is an example of using a correlated subquery to update table1. Since there is no predicate to restrict the number of rows scanned in table1 in the update -- every row will in fact be updated by definition.
If you wanted to update the join, it would look like this:
ops$tkyte_at_8i> create table t1 ( xx int, xz int );
Table created.
ops$tkyte_at_8i> create table t2 ( xx int primary key, xy int );
Table created.
ops$tkyte_at_8i> create table t3 ( xy int primary key, xz int );
Table created.
ops$tkyte_at_8i> ops$tkyte_at_8i> ops$tkyte_at_8i> update 2 (select table1.xz t1_xz, table3.xz t3_xz 3 from t1 table1, t2 table2, t3 table3 4 where table1.xx = table2.xx 5 and table2.xy =table3.xy)
0 rows updated.
if you wanted to use the correlated subquery it would look like this:
update table1
set table1.xz = (select table3.xz
from table2,table3
where table1.xx = table2.xx
and table2.xy =table3.xy)
WHERE EXISTS (select table3.xz
from table2,table3
where table1.xx = table2.xx
and table2.xy =table3.xy)
> Thanks
> -Deepa
>
> Thomas J. Kyte wrote:
>
> > In article <3948e631_at_news.mdh.se>,
> > "Ulf Körnings" <ulf.kornings_at_mdh.se> wrote:
> > >
> > > Hi!
> > >
> > > I would like to do somthing like this in SQL:
> > >
> > > update table_a,table_b set table_a.data=table_b.data where
> > > table_a.kod=table_b.kod
> > >
> > > Is it possible?
> > >
> > > /Ulf
> > >
> > >
> > There are at least 2 ways to perform this sort of co-related
> > update correctly. I'll show my preferred method (update a join)
> > and then another method that'll work if you cannot put a unique
> > constraint on LOOKUP(keyname) (which is needed for the join
> > update).
> >
> > Here are the test tables:
> >
> > scott_at_ORA734.WORLD> create table name
> > 2 ( keyname int,
> > 3 columnName varchar2(25)
> > 4 )
> > 5 /
> > Table created.
> >
> > scott_at_ORA734.WORLD> create table lookup
> > 2 ( keyname int PRIMARY KEY,
> > 3 value varchar2(25),
> > 4 otherColumn int
> > 5 )
> > 6 /
> > Table created.
> >
> > scott_at_ORA734.WORLD> insert into name values ( 100, 'Original
> > Data' );
> > 1 row created.
> >
> > scott_at_ORA734.WORLD> insert into name values ( 200, 'Original
> > Data' );
> > 1 row created.
> >
> > scott_at_ORA734.WORLD> insert into lookup values ( 100, 'New Data',
> > 1 );
> > 1 row created.
> >
> > scott_at_ORA734.WORLD> commit;
> > Commit complete.
> >
> > here is the "other_value" parameter you are using in the above
> > update you attempted...
> >
> > scott_at_ORA734.WORLD> variable other_value number
> > scott_at_ORA734.WORLD> exec :other_value := 1
> > PL/SQL procedure successfully completed.
> >
> > scott_at_ORA734.WORLD> select * from name;
> >
> > KEYNAME COLUMNNAME
> > ---------- -------------------------
> > 100 Original Data
> > 200 Original Data
> >
> > Here we update a join. We can only modify the columns in one
> > of the tables and the other tables we are *NOT* modifying must
> > be "key preserved" -- that is, we must be able to verify that at
> > most one record will be returned when we join NAME to this other
> > table. In order to do that, keyname in LOOKUP must either be a
> > primary key or have a unique constraint applied to it...
> >
> > scott_at_ORA734.WORLD> update
> > 2 ( select columnName, value
> > 3 from name, lookup
> > 4 where name.keyname = lookup.keyname
> > 5 and lookup.otherColumn = :other_value )
> > 6 set columnName = value
> > 7 /
> >
> > 1 row updated.
> >
> > scott_at_ORA734.WORLD> select * from name;
> >
> > KEYNAME COLUMNNAME
> > ---------- -------------------------
> > 100 New Data
> > 200 Original Data
> >
> > See, the other data is untouched and only the rows we wanted
> > are updated..
> >
> > scott_at_ORA734.WORLD> rollback;
> > Rollback complete.
> >
> > scott_at_ORA734.WORLD> select * from name;
> >
> > KEYNAME COLUMNNAME
> > ---------- -------------------------
> > 100 Original Data
> > 200 Original Data
> >
> > Now, this way will work with no constraints on anything -- you
> > do not need the primary key/unique constraint on lookup (but you
> > better be sure the subquery returns 0 or 1 records!).
> >
> > It is very much like your update, just has a where clause so
> > that only rows that we find matches for are actually updated...
> >
> > scott_at_ORA734.WORLD> update name
> > 2 set columnName = ( select value
> > 3 from lookup
> > 4 where lookup.keyname = name.keyname
> > 5 and otherColumn = :other_value )
> > 6 where exists ( select value
> > 7 from lookup
> > 8 where lookup.keyname = name.keyname
> > 9 and otherColumn = :other_value )
> > 10 /
> >
> > 1 row updated.
> >
> > scott_at_ORA734.WORLD> select * from name;
> >
> > KEYNAME COLUMNNAME
> > ---------- -------------------------
> > 100 New Data
> > 200 Original Data
> >
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Tue Jun 20 2000 - 00:00:00 CDT
 |
 |