Re: SQL Update

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.

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.

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 Sun Jun 18 2000 - 00:00:00 CDT