Update Table from another Table Values return Error

Question

I have two Tables Employee_Data and IOT_Codes_Temp, I want to Update the records of Employee_Data.IOT_Codes with IOT_Codes_Temp.IOT_Codes. Below is my Query:

UPDATE employee_data t1 
SET    ( t1.iot_codes, t1.iot_codes_numeric ) = (SELECT t2.iot_code, 
                                                        t2.iot_code_numeric 
                                                 FROM   ot_codes_temp t2 
                                                 WHERE 
       t2.office = t1.department); 

But it returns Error message: ORA-01427: single-row subquery returns more than one row. Help me how to get rid of this error.

Answer 1

The syntax you are trying to use is of INSERT Statement .. This will do it for you ..

DECLARE @IOT_CODE INT;
DECLARE @IOT_CODE_NUMERIC INT;

SET @IOT_CODE=select t2.IOT_CODE From OT_CODES_TEMP t2,employee_data t1 Where t2.OFFICE=t1.DEPARTMENT;

SET @IOT_CODE_NUMERIC =select t2.IOT_CODE_NUMERIC From OT_CODES_TEMP t2,employee_data t1 Where t2.OFFICE=t1.DEPARTMENT;

update employee_data 
set 
IOT_CODES=@IOT_CODE ,
IOT_CODES_NUMERIC=@IOT_CODE_NUMERIC;

EDIT

I looked into this further .. you can also do something like this .. Reference

 UPDATE 
 (SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
 ) t
 SET t.OLD = t.NEW