Single Row Subquery Returns more than One Row in Oracle

Question

I am relatively new to Oracle SQL and have an assignment for a Database Applications course that I am taking, where a part of the assignment is to transfer some data (empno, bonus_date, job, sal, and comm) from one table to another using SQL on the command line.

In order to transfer the data, I am using subqueries, as well as the to_date() function (the assignments specifies to insert the current date into the new table, as it's being added, in addition to the data from the old table), but keep receiving the following error:

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

I e-mailed my professor, who suggested that the error may have been a result of an extra space with my to_date() function, but after changing it, the error still persists.

I also tried changing the formatting of the subqueries, but also to no avail. Here are the two tables that I am working with (emp is the old table and emp_hist is the new table):

emp (old) table:

 desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              CHAR(10)
 JOB                                                CHAR(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBER(2)

emp_hist (new) table:

desc emp_hist;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 BONUS_DATE                                NOT NULL DATE
 JOB                                                CHAR(9)
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)

Here are the queries (and error messages) that I have been using and receiving:

SQL> insert into emp_hist(empno,bonus_date,job,sal,comm)
  2  values((select empno from emp),(to_date('10/05/2015','mm/dd/yyyy')),
  3  (select job from emp),(select sal from emp),(select comm from emp));
values((select empno from emp),(to_date('10/05/2015','mm/dd/yyyy')),
       *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row 

Any insight into this would be greatly appreciated! If any additional information is needed, please let me know and I'll try my best to provide it!

Thank you!

Answer 1

If you are just learning Oracle, you really don't need insert . . . values(). A more powerful statement is insert . . . select. What you are trying to do is:

insert into emp_hist(empno, bonus_date, job, sal, comm)
    select empno, to_date('10/05/2015','mm/dd/yyyy'), job, sal, comm
    from emp;