Updating parent table with value from child table

[sweetleaf]

Hi There,

I was wondering if anyone could help me solve my riddle?

What i'm trying to do is dynamically update a column from parent table A with the value of a certain column in
child table B under certain conditions (child table B will often contain more than 1 corresponding record but I am
grabbing a value only if the value is common to all such records ie. update the PRIORITY column of parent table A with 3 only if related-records in child table B all have PRIORITY value of 3):

Heres the meat of my stored procedure which has no inputs or outputs :
(i get "ORA-01427: single-row subquery returns more than one row" error)

UPDATE Statement_Message
SET dt_pri =(SELECT distinct a.priority
FROM STATEMENT_MESSAGE_DATE a,
STATEMENT_MESSAGE_DATE b, /* 2nd alias to screen out if multiple rows bear same or different values */
Statement_Message c
WHERE a.START_DATE <> b.START_DATE and
a.END_DATE <> b.END_DATE and
a.PRIORITY = b.PRIORITY) and
a.stmt_message_no = c.stmt_message_no)
WHERE stmt_message_no IN
(select a.stmt_message_no
from STATEMENT_MESSAGE_DATE a,
STATEMENT_MESSAGE_DATE b
where a.stmt_message_no = b.stmt_message_no)


Any help is much appreciated Thanks,

 

[try]

have you tried to use where exist?
check the SQL reference manual for more details!