November 9, 2009 at 12:47 pm
Hi all,
Can´t see what i´m doing wrong in this statement. Giving me an error message when i´m running an update statement
subquery cannot return more than one row
I want to update seq in t1 with the seq in t2 where t3.class_name = t2.class_name
Statement below
UPDATE t1
SET t1.seq =
(
SELECT t2.seq
FROM t2
WHERE EXISTS
(
SELECT t3.class_name
FROM t3
WHERE t3.class_name = t2.class_name
AND t3.obj_num = t1.obj_num
AND t3.name = t1.name
)
)
Regards
November 9, 2009 at 12:59 pm
It means your first inner select is returning more than one row in at least one case.
Turn it into:
select t1.seq, t2.seq
from t1
inner join t3
on t1.obj_num = t3.obj_num
and t1.name = t3.name
inner join t2
on t3.class_name = t2.class_name
group by t1.seq, t2.seq
having count(*) > 1;
That should give you the rows where you're getting more than one row in the subquery.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2009 at 2:31 pm
Thx a lot for a quick answer! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply