How to solve single-row subquery returns more than one row

Question

I have an employee table and it contains salary table. I want to give %10 increase to all current employees. I tried to update all employees' salary dates to specific date but I encountered problem with single-row subquery.

My database like this:

CREATE TYPE TEMPORAL_VARCHAR AS OBJECT (
VALID_TIME_LOWER_BOUND DATE,
VALID_TIME_UPPER_BOUND DATE,
VALUE_PART VARCHAR2(50) );
CREATE TYPE TEMPORAL_NUMBER AS OBJECT (
VALID_TIME_LOWER_BOUND DATE,
VALID_TIME_UPPER_BOUND DATE,
VALUE_PART NUMBER );

CREATE TYPE NAME_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE ADDRESS_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE DEPARTMENT_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE MANAGER_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE SALARY_TYPE AS TABLE OF TEMPORAL_NUMBER;

CREATE TABLE EMPLOYEE (
SSN NUMBER primary key,
NAME NAME_TYPE,
ADDRESS ADDRESS_TYPE ,
BIRTH_DATE DATE,
MANAGER MANAGER_TYPE ,
DEPARTMENT DEPARTMENT_TYPE,
SALARY SALARY_TYPE
)
NESTED TABLE NAME STORE AS NAME_TABLE,
NESTED TABLE ADDRESS STORE AS ADDRESS_TABLE,
NESTED TABLE MANAGER STORE AS MANAGER_TABLE,
NESTED TABLE DEPARTMENT STORE AS DEPARTMENT_TABLE,
NESTED TABLE SALARY STORE AS SALARY_TABLE
;

How to solve this problem? I tried to do this

UPDATE TABLE(
SELECT E.SALARY
FROM EMPLOYEE E
) SAL
SET SAL.VALID_TIME_UPPER_BOUND = '11.16.2015'
WHERE SAL.VALID_TIME_UPPER_BOUND = TO_DATE('12.31.9999','MM.DD.YYYY');

The error message means that a sub-query returns more than one row. Which means your sub-query has a flawed WHERE clause for the data it's supposed to be filtering. However you have posted neither a SELECT statement with a sub-query nor any sample date. So it beats me why you think we can give you any help. — APC, Nov 15 '15 at 21:46
duplicate? here is same question: http://stackoverflow.com/questions/3256433/update-multiple-records-in-multiple-nested-tables-in-oracle — are, Nov 15 '15 at 22:10
Does `UPDATE SALARY SET VALID_TIME_UPPER_BOUND = TO_DATE('11.16.2015', 'MM.DD.YYYY') WHERE VALID_UPPER_TIME_BOUND = TO_DATE('12.31.9999','MM.DD.YYYY')` work as expected? — Bob Jarvis - Слава Україні, Nov 15 '15 at 22:34
i used ' `alter session set NLS_DATE_FORMAT='MM.DD.YYYY';` for it — Yusuf Çakmak, Nov 15 '15 at 22:37
The nested table locator hint (in the duplicate question suggested by @are )will solve your problem — APC, Nov 15 '15 at 22:44
You are entering a world of hurt by using table types. No one actually uses these things IRL - I suggest you drop the idea pronto. — Bohemian, Nov 16 '15 at 00:47

score 0 · Answer 1 · answered Nov 15 '15 at 22:44

1st it can be duplicate

2nd see sample here

3rd in your code you need bring the where condition into select

UPDATE TABLE(
  SELECT E.SALARY
    FROM EMPLOYEE E 
      WHERE ssn in (SELECT ssn FROM EMPLOYEE e
   WHERE to_date('01.01.2015','mm.dd.yyyy')  in (
      SELECT VALID_TIME_UPPER_BOUND FROM TABLE(e.salary)
     ) 
   )
) SAL
SET SAL.VALID_TIME_UPPER_BOUND =  to_date('01.01.9999','mm.dd.yyyy')

test data

INSERT INTO EMPLOYEE(SSN, salary) values (1, SALARY_TYPE ());
INSERT INTO EMPLOYEE(SSN, salary) values (2, SALARY_TYPE ());
INSERT INTO EMPLOYEE(SSN, salary) values (3, SALARY_TYPE ());

INSERT INTO TABLE(SELECT salary FROM EMPLOYEE 
   WHERE ssn = 1)
   VALUES (to_date('01.01.2005','mm.dd.yyyy'), to_date('01.01.2015','mm.dd.yyyy'), 1);

INSERT INTO TABLE(SELECT salary FROM EMPLOYEE 
   WHERE ssn = 2)
   VALUES (to_date('02.02.2005','mm.dd.yyyy'), to_date('02.02.2015','mm.dd.yyyy'), 2);


INSERT INTO TABLE(SELECT salary FROM EMPLOYEE 
   WHERE ssn = 3)
   VALUES (to_date('03.03.2005','mm.dd.yyyy'), to_date('03.03.2015','mm.dd.yyyy'), 3);

p.s do you really need the complexity?

score 0 · Answer 2 · answered Nov 16 '15 at 00:43

I solved my problem using iteration like this

BEGIN
  FOR employees IN (SELECT SSN FROM EMPLOYEE)
  LOOP
UPDATE TABLE(
SELECT E.SALARY
FROM EMPLOYEE E
WHERE E.SSN = employees.SSN
) SAL
SET SAL.VALID_TIME_UPPER_BOUND = '11.16.2015'
WHERE SAL.VALID_TIME_UPPER_BOUND = TO_DATE('12.31.9999','MM.DD.YYYY');
  END LOOP;
END;

How to solve single-row subquery returns more than one row

2 Answers2