CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
SQL N3rd
SQL N3rd

Reputation: 25

Single-Row returns more than one row error in Oracle Apex when using Insert function - SQL

I'm having much trouble with the Oracle Live SQL tutorials for the Oracle Apex Workspace. In particular, I'm stuck on Module 3: Inserting data because of the following:

The tutorial wants me to run the following code to add to the employees table:

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Sam Smith','Programmer', 
    5000, 
  (select deptno 
  from departments 
  where name = 'Development'));

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Mara Martin','Analyst', 
   6000, 
   (select deptno 
   from departments 
   where name = 'Finance'));

insert into EMPLOYEES 
   (name, job, salary, deptno) 
   values
   ('Yun Yates','Analyst', 
   5500, 
   (select deptno 
   from departments 
   where name = 'Development'));

However, when I attempt to run this code (which was copied and pasted from the tutorial into my Oracle Apex Workspace), I get the following error:

ORA-01427: single-row subquery returns more than one row ORA-06512: at "SYS.DBMS_SQL", line 1721

I checked my Objects aka tables to ensure that there were no tables with duplicate column names, however, I don't have any tables with any duplicates.

Can anyone help me with this? I'm quite new to both SQL and the Oracle Apex workspace.

Upvotes: 0

Views: 569

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

This suggests that your departments has more than one row for certain names. You can determine which:

select name
from departments
group by name
having count(*) > 1;

There probably should not be duplicates. You probably accidentally loaded the data twice into the table, or something like that.

Upvotes: 0

Related Questions