Multi-table join update in Oracle

Suppose we have two tables: test1 and Test2.

Create   Table Test1 (No Number , Name Varchar2 ( 10 ));

Create   Table Test2 (No Number , Name Varchar2 ( 10 ));

Insert   Into Test1 Values ( 1 , ' A ' );

Insert   Into Test1 Values ( 2 , ' B ' );

Insert   Into Test1 Values ( 3 , ' C ' );

Insert   Into Test2 Values ( 1 , ' AA ' );

Insert   Into Test2 Values ( 2 , ' Bb ' );

So far:

Test1

No name

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

1

2 B

3 C

Test2

No name

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

1 aa

2 bb

If you want to update the name field of a record in test1 and Test2 with the no field equal to that in Test2

Segment value.

That is, the following results:

Test1

No name

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

1 aa

2 bb

3 C

The following statements

Update test1 a set name = (Select name from Test2 B where a. No = B. No );

The effect is:

Test1

No name

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

1 aa

2 bb

3

To achieve the desired effect, you can use the following statement.
Update Test1 Set Name = ( Select Name From Test2 B Where A. No = B. No) Where

Exists ( Select Name From Test2 B Where A. No = B. No );

You can also use the following statement:
Update(SelectA. Name aname, B. Name bnameFromTest1 A, Test2 BWhere

A. No=B. No)SetAname=Bname;

However, there is a premise that no in Table Test2 is set as the primary key.

Alter table Test2 add primary key (NO );