cant update a column from table it returns single-row subquery returns more than one

Question

When i execute the following query, i get the message like

Ora-01427 single-row subquery returns more than one row

I am trying to update "City" column in Table A From another table. How would I do this?

1. table A: Name, PER_code(it also has duplicated value or null), city, PD_code
2. table B: Name, PER_code(No duplicated value,Maybe null), city, Postal_code

The update statement:

UPDATE A
SET (A.city) =
(SELECT B.city
FROM B
INNER JOIN A
ON A.per_code=B.per_code 
WHERE A.per_code is not null)

Answer 1

Since there are duplicate values but you select only one field, you modify your query from

UPDATE A SET (A.city) = (SELECT B.city FROM B INNER JOIN A ON 
A.per_code=B.per_code WHERE A.per_code is not null)

to

UPDATE A SET (A.city) = (SELECT DISTINCT B.city FROM B INNER JOIN A ON 
A.per_code=B.per_code WHERE A.per_code is not null)

The distinct operator will allow you to keep a single value if it's duplicated in the table B. If there are multiple distinct values, you will have to look at your data and make a decision about which value should be used in the other table.

Answer 2

You can also try MERGE INTO selecting DISTINCT records.

MERGE INTO A d
USING
( SELECT DISTINCT per_code, city FROM B ) s 
ON ( s.per_code = d.per_code ) 
WHEN MATCHED THEN UPDATE SET d.City = s.City 
WHERE d.per_code IS NOT NULL;

Answer 3

I think you intend a correlated subquery:

UPDATE A
    SET city = (SELECT B.city
                FROM B 
                WHERE A.per_code = B.per_code
               )
    WHERE A.per_code is not null;

EDIT:

The above should work given the constraints in the original question. If it does not, it is easily adaptable:

UPDATE A
    SET city = (SELECT B.city
                FROM B 
                WHERE A.per_code = B.per_code AND rownum = 1
               )
    WHERE A.per_code is not null;