I am trying to insert 25 users, user1...25 into Members with the same GR_ID = 2 with command:

insert into MEMBERS (GR_id, username) values (2, (select username from USERS where username like 'test%'));

but get error:

  1. 00000 - "single-row subquery returns more than one row"

Any advice?

  • 3
    Everything is in Oracle error message – you're attempting to put value into column, but there are more than one of results returned by subquery. – Yasir Arsanukaev Aug 11 '14 at 11:48

When you use the values clause in Oracle, you can only provide comma-separated values.

If you want to select one or more rows from another table, you have to use this syntax:

insert into <table>(<col1>,<col2>,...,<coln>)
select <col1>,<col2>,...,<coln>
from ...;

In your case:

insert into MEMBERS(GR_id, username)
select 2, username
from USERS
where username like 'test%';
  • I wouldn't call a subquery a value -- although it does work if it returns a single row. What wording do you suggest? – Colin 't Hart Aug 11 '14 at 12:25
  • 1
    I don't think you mean to, but it sounds like you are saying this doesn't work? – Jack Douglas Aug 11 '14 at 13:56

The query

select username from USERS where username like 'test%'

.. returns more than one row. If you limit it to return only the first row, or rephrase the WHERE clause to return only a single row, you should be ok.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.