-2

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
ZahidKakar
  • 213
  • 1
  • 7
  • 23

2 Answers2

-1

The error message says it all: your "update" syntax requires one row, but your subselect is returning multiple rows.

OPTION 1: qualify your subselect so that it only returns one row

OPTION 2: change your "=" to "IN" (or "ANY", "A"" or "NOT")

It depends on what you're trying to do.

FoggyDay
  • 11,962
  • 4
  • 34
  • 48
  • I want to update multiple data with multiple rows for e.g. Information Technology has IOT_Code IT, so IT will be assign to All those employees in Employee_Data who has Department=Information Technology. – ZahidKakar Oct 01 '14 at 06:31
-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
miken32
  • 42,008
  • 16
  • 111
  • 154
Rafay
  • 603
  • 2
  • 9
  • 24