CODEWITHSUNDEEP
oracle11g
CCC
CCC

Reputation: 121

ORA-01427: single-row subquery returns more than one row when inserting multiple rows

I am trying to assign or give all permissions of a user to another given user, 13053 but facing this Oracle error, ORA-01427: single-row subquery returns more than one row and i know exactly which part of my SQL statement shown below is returning this error but failed to handle it because what i want to achieve is to give those multiple rows returned to the given user with an id of 13053.

My attempt

INSERT INTO userpermissions (
     userid,permissionid
) VALUES (
     13053,( SELECT permissionid
        FROM userpermissions
        WHERE userid = ( SELECT userid
                         FROM users
                         WHERE username = '200376'
                       )
      )
);

Any help ?

Thanks in advance.

Upvotes: 0

Views: 909

Answers (1)

Tad Harrison
Tad Harrison

Reputation: 1293

A rewrite ought to do the trick:

INSERT INTO USERPERMISSIONS(
  USERID,
  PERMISSIONID
)
 SELECT 13053 AS USERID,
        p.PERMISSIONID
   FROM USERPERMISSIONS p
  WHERE p.userid = (SELECT userid FROM users WHERE username = '200376');

The problem with the original insert is that you are using single-row insert syntax when you are really trying to insert a set of rows.

Including the target userid as a literal is one way to make the set of rows look the way I am assuming you intend.

Upvotes: 1

Related Questions