ORACLE SQL: how to update a table based in another column?

Question

So, i have two tables, INTERNAMENTO and DOC_ADMISSAO2. i want to update a column from table INTERNAMENTO named DIASINTERNAMENTO with the same values of the table DOC_ADMISSAO2 called DIASADMISSAO, with some conditions..

update INTERNAMENTO a
set a.DIASINTERNAMENTO = (
       select b.DIASADMISSAO
       from DOC_ADMISSAO2 b
       where (a.EPISODIO = b.EPISODIO) AND (a.DATACRIACAO <= b.DATACRIACAO));

It gives me an error:

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

Any advice?

either you must add a criterion to the where clause to get always a single row or use an aggregation on b.DIASADMISSAO (i.E. MAX(b.DIASADMISSAO) ) in the subselect — Turo, Jun 26 '16 at 20:20
In addition to what Gordon just offered, presumably your tables show details of "hospitalizations" and "admissions"... for various patients? Don't you need the patient_id to be the same in the where clause? — , Jun 26 '16 at 20:29

score 5 · Accepted Answer · answered Jun 26 '16 at 20:24

The error is quite clear. The subquery is returning more than one row. You can get one row by either adding and rownum = 1 or adding an aggregation function:

update INTERNAMENTO a
set DIASINTERNAMENTO = (
       select max(b.DIASADMISSAO)
       from DOC_ADMISSAO2 b
       where (a.EPISODIO = b.EPISODIO) AND (a.DATACRIACAO <= b.DATACRIACAO)
     );

If you happen to want the most recent value of DISASMISSAO for the EPISODIO based on DATACRIACAO, then you can use keep:

update INTERNAMENTO a
set DIASINTERNAMENTO = (
       select max(b.DIASADMISSAO) keep (dense_rank first order by b.DATACRIACAO desc)
       from DOC_ADMISSAO2 b
       where (a.EPISODIO = b.EPISODIO) AND (a.DATACRIACAO <= b.DATACRIACAO)
     );

score 1 · Answer 2 · answered Jun 26 '16 at 20:28

It means that there are multiple rows in doc_admissao2 for some value of internamento.episodio where b.datacriacao is greater than or equal to a.datacriacao.

To find out which ones, try something like this (untested):

SELECT a.episodio, count(*)
  FROM internamento a JOIN doc_admissao2 b ON a.episodio = b.episodio
 WHERE a.DATACRIACAO <= b.DATACRIACAO
 GROUP BY a.episodio
HAVING count(*) > 1;

ORACLE SQL: how to update a table based in another column?

2 Answers2