oracle利用表更新表,怎么样根据小表的数据更新大表的数据？

可以参考一下

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@ORA734.WORLD> create table name

2  ( keyname int,

3    columnName varchar2(25)

4  )

5  /

Table created.

scott@ORA734.WORLD> create table lookup

2  ( keyname int PRIMARY KEY,

3    value varchar2(25),

4    otherColumn int

5  )

6  /

Table created.

scott@ORA734.WORLD> insert into name values ( 100, 'Original Data' );

1 row created.

scott@ORA734.WORLD> insert into name values ( 200, 'Original Data' );

1 row created.

scott@ORA734.WORLD> insert into lookup values ( 100, 'New Data', 1 );

1 row created.

scott@ORA734.WORLD> commit;

Commit complete.

here is the "other_value" parameter you are using in the above update you

attempted...

scott@ORA734.WORLD> variable other_value number

scott@ORA734.WORLD> exec

ther_value := 1

PL/SQL procedure successfully completed.

scott@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@ORA734.WORLD> update

2    ( select columnName, value

3        from name, lookup

4       where name.keyname = lookup.keyname

5         and lookup.otherColumn =

ther_value )

6     set columnName = value

7  /

1 row updated.

scott@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@ORA734.WORLD> rollback;

Rollback complete.

scott@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@ORA734.WORLD> update name

2     set columnName = ( select value

3                          from lookup

4                         where lookup.keyname = name.keyname

5                           and otherColumn =

ther_value )

6   where exists ( select value

7                    from lookup

8                   where lookup.keyname = name.keyname

9                     and otherColumn =

ther_value )

10  /

1 row updated.

scott@ORA734.WORLD> select * from name;

KEYNAME COLUMNNAME

---------- -------------------------

100 New Data

200 Original Data

In general, if "NAME" was very very small and "LOOKUP" was very very large (yet

indexed on keyname/othercolumn/value) -- the where exists would be very safe.

update t

set ( x,y,z)

= ( select x,y,z

from t1

where t.x ==t1.x)

where exists

( select x,y,z

from t1

where t.x ==t1.x)

Why is it necessary to give the whole query, woudnt it suffice to just say

update t

set ( x,y,z)

= ( select x,y,z

from t1

where t.x ==t1.x)

where t.x =: some value we know

Followup:

that doesn't make sense.  where did you get "some value you know"

the first query with the where exists -- that will update every row in T with a

value in T2 given that a row exists in T2 to update t with

your query will update every row in T where t.x = some_value -- if no values

exist in T2, t.x,y,z will be set to NULL

they are totally different