Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL Update
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
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 Thu Jun 15 2000 - 00:00:00 CDT