Oracle SQL correlated update
15,830
Solution 1
If there is a one-to-many relationship between t1 and t2 or between t2 and t3 you will get many matches for each row in t1. If you know that all rows in t3 that belong to the same row in t1 have the same value in d, then you can use DISTINCT
to remove (identical) duplicates.
UPDATE table1 t1
SET t1.c = (select DISTINCT t3.d
from table2 t2, table3 t3
where t2.b = t3.b and t1.a = t2.a)
WHERE EXISTS ( SELECT 1 FROM table2 t2, table3 t3 WHERE t1.c = t3.c and t1.a = t2.a);
Solution 2
Sorry for the confusion but I solved it:
UPDATE table t1
SET t1.c = (select t3.d from table3 t3, table2 t2
where t1.a = t2.a and t2.b = t3.b and t3.c = t1.c)
WHERE EXISTS ( SELECT 1 FROM table1 t1, table2 t2 WHERE t1.a = t2.a and t2.b = t3.b and t3.c = t1.c)
Solution 3
You have a subquery that is returning more than one row. Use rownum
to get just one row:
UPDATE table1 t1
SET t1.c = (select d
from (select t3.d
from table2 t2 join table3 t3
on t2.b = t3.b
where t1.a = t2.a
) t
where rownum = 1
)
WHERE EXISTS ( SELECT 1 FROM table2 t2, table3 t3 WHERE t1.c = t3.c and t1.a = t2.a);
Author by
President Camacho
Updated on June 14, 2022Comments
-
President Camacho almost 2 years
I got three tables:
t1.columns: a,c t2.columns: a,b t3.columns: b,c,d
Now what I want is to update t1.c with t3.d. But I can't just update t1 from t3 using t1.c = t3.c I also have to go though t3.b = t2.b and t1.a = t2.a.
I've tried something like this:
UPDATE table1 t1 SET t1.c = (select t3.d from table2 t2, table3 t3 where t2.b = t3.b and t1.a = t2.a) WHERE EXISTS ( SELECT 1 FROM table2 t2, table3 t3 WHERE t1.c = t3.c and t1.a = t2.a);
This code generates error-msg: ORA-01427: single-row subquery returns more than one row
-
President Camacho about 11 years
t2.a
have a one to many relation tot1.a
(t2.a
is the one andt1.a
is the many) Everywhere else is a one to one relation. -
Klas Lindbäck about 11 yearsHmm, I noticed that the join conditions are different in the
EXISTS
clause. Should it really bet1.c = t3.c
and nott2.c = t3.c
? -
President Camacho about 11 yearsForgot: t2.b have a one to many relation to t3.b where t2 got the one and t3 got the many.
-
President Camacho about 11 yearsHmm, think I'm explaining it wrong, there should'nt be any doubles. I shall review me question and update it.