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?
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)
);
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;
