Update operations associated with two oracle tables

Create table a (no number notnull, name varchar (10) notnull, loc varchar (10) notnull );

Create table B (no number notnull, name varchar (10) notnull, loc varchar (10) notnull );

Insert into a values (10, 'aaa', 'aremak1 ');

Insert into a values (20, 'bbb ', 'aremak2 ');

Insert into a values (30, 'ccc ', 'aremak3 ');

Insert into B values (1, 'aaa', 'bremak1 ');

Insert into B values (2, 'bbb ', 'bremak2 ');

Insert into B values (3, 'ddd ', 'bremak3 ');

SELECT * FROM;

ID NAME LOC

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

10 AAA Aremak1

20 BBB Aremak2

30 CCC Aremak3

SELECT * FROM B;

ID NAME LOC

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

1 AAA Bremak1

2 BBB Bremak2

3 DDD Bremak3

Update table B so that the IDs with the same name are updated to the IDs of Table.

Sql1 in oracle

Update B set id = select a. id from a where a. name = B. name );

When the above statement is updated, because the record name is 'ddd 'does not exist in Table a, this row cannot obtain data, and the id is set to null.

ID NAME LOC

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

10 AAA Bremak1

20 BBB Bremak2

DDD Bremak3

To avoid this error, you must change sql1 to sql2.

Update B set id = select a. id from a where a. name = B. name) where exists select 1 from a where a. name = B. name );

ID NAME LOC

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

10 AAA Bremak1

20 BBB Bremak2

3 DDD Bremak3

Error summary:

1. sql1 select a. id from cannot be followed by a, B) update B set id = select a. id from a, B where a. name = B. name );

Will cause the error single-row subquery returns more than one row

2. SQL 2 exists cannot be followed by a, B select 1 from a, B where a. name = B. name,

The exists statement is invalid, but no error is reported.