Update table with values from another table

Palesa Mafanya

6y952SQL

This is the query I ran but it gave me error message:

UPDATE B_EMPLOYEES b

SET SUBURB=(SELECT SUBURB FROM B_PERSAL_DUMP a

WHERE a.IDNUM=b.id_number)

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jefferson S. Motta6y ago
I mean: Select DISTINCT (fields....)...
Jefferson S. Motta6y ago
On your select add DISTINCT.

Hi Rajanikant,

I get this error with your query.

ORA-01427: single-row subquery returns more than one row
Rajanikant Hawaldar6y ago
If your issue not yet resolved post both table structure
Jignesh Kumar6y ago
Hi,
Please try this one,
MERGE INTO B_EMPLOYEES t1
USING (select IDNUM, SUBURB from B_PERSAL_DUMP) t2
ON (t1.id_number= t2.IDNUM)
WHEN MATCHED THEN
UPDATE SET t1.SUBURB = t2.SUBURB;
Palesa Mafanya6y ago
Hi Rajanikant,

I get this error with your query.

ORA-01427: single-row subquery returns more than one row
Palesa Mafanya6y ago
apologies, meant to say queries don't work
Palesa Mafanya6y ago
i'm using oracle sql could that be the reason why queries do work? I'm new to oracle
Jignesh Kumar6y ago
Hi,

Please try this query,
UPDATE
    B_EMPLOYEES
SET
    SUBURB = a.SUBURB
FROM
    B_PERSAL_DUMP a
    INNER JOIN B_EMPLOYEES b
        ON a.IDNUM=b.id_number
Rajanikant Hawaldar6y ago
Hey go through below one this works in Oracle, my previous query will work in SQL Server
UPDATE B_EMPLOYEES b
SET b.SUBURB=(SELECT SUBURB FROM B_PERSAL_DUMP a
                          WHERE  a.IDNUM=b.id_number)
Palesa Mafanya6y ago
Hi Rajanikant

I tried your query but I get an error :

ORA-00933: SQL command not properly ended
Rajanikant Hawaldar6y ago
Hello Palesa Mafanya, Please go check below query
UPDATE B_EMPLOYEES b
SET b.SUBURB=a.SUBURB FROM B_EMPLOYEES b INNER JOIN B_PERSAL_DUMP a
                          ON  a.IDNUM=b.id_number
In your query, subquery returns multiple SUBURB that is why your getting error. Thanks
Jignesh Kumar6y ago
Hi, Please check this link which will help you. https://www.mysqltutorial.org/mysql-update-join/