Update table with values from another table

Question

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)

Jefferson S. Motta · Answer

I mean: Select DISTINCT (fields....)...

Jefferson S. Motta · Answer

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 Hawaldar · Answer

If your issue not yet resolved post both table structure

Jignesh Kumar · Answer

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 Mafanya · Answer

Hi Rajanikant, I get this error with your query. ORA-01427: single-row subquery returns more than one row

Palesa Mafanya · Answer

apologies, meant to say queries don't work

Palesa Mafanya · Answer

i'm using oracle sql could that be the reason why queries do work? I'm new to oracle

Jignesh Kumar · Answer

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 Hawaldar · Answer

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 Mafanya · Answer

Hi Rajanikant I tried your query but I get an error : ORA-00933: SQL command not properly ended

Rajanikant Hawaldar · Answer

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 Kumar · Answer

Hi, Please check this link which will help you. https://www.mysqltutorial.org/mysql-update-join/