CODEWITHSUNDEEP
oracle-databasesql-updatesubqueryoracle-sqldeveloper
Slimex
Slimex

Reputation: 13

Trying to update table but i always get ora-01427

So i am trying to update my Vorschlagspakete table with some values i got from other tables. Precisely there are 3 values i want to write into the main table. Atm it looks like this:

update vorschlagspakete
set (paketid, verkaufsstelleid) = (
  select k.paketid, k.verkaufsstelleid
  from Konfiguration k, bewertung b
  where k.konfigurationsid = b.konfigurationsid
  group by k.paketid, k.verkaufsstelleid
  having avg(b.sterne) >= 5);

But every time i tried this it results into ora-01427.

Upvotes: 0

Views: 66

Answers (1)

Littlefoot
Littlefoot

Reputation: 143153

Error you got, ORA-01427, means too many rows (were returned by subquery). For example based on Scott's schema (as I don't have your tables), it looks like this:

SQL> update emp e set
  2    (e.ename, e.job) = (select d.dname, d.loc from dept d);
  (e.ename, e.job) = (select d.dname, d.loc from dept d)
                      *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Why wouldn't it work? Because subquery returns more than a single row!

SQL> select d.dname, d.loc from dept d;

DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

SQL>

So, how would you put all those values into a single row of the EMP table? That won't work, obviously, so you have to do something to restrict number of rows. How? Well, it depends.

  • sometimes DISTINCT helps, e.g.

    select distinct d.dname, d.loc from dept d

  • sometimes additional WHERE condition helps, e.g.

    select d.dname, d.loc from dept d
where d.location = 'NEW YORK'

  • sometimes joining with the table to be updated helps, e.g.

    select d.dname, d.loc from dept d where d.deptno = e.deptno

    which leads to

    SQL> update emp e set
  2    (e.ename, e.job) = (select d.dname, d.loc from dept d where d.deptno =       e.deptno);

14 rows updated.

What should you do? I don't know, we don't have your data. See whether something written above helps.

Upvotes: 1

Related Questions