problem with update in procedure

From: Michael <mwaples_at_waples.net>
Date: 24 Jan 2002 08:36:07 -0800
Message-ID: <3d0e7fa2.0201240836.6d57b552_at_posting.google.com>

Im not getting an update as I expect from a procedure, if I run the update part from sqlplus it works correctly, as does the view that the update selects from.

But when I run the procedure the threads table is updated correctly all except the threads row that corresponded to the deleted message. It leaves the fields that correspond to the message table in the join as NULL.

I'm sure its something simple thats wrong but I just cant figure it out.

the relevant part of the procedure is -

DELETE from messages WHERE thread_id = threadid and message_id = messageid;

/* update threads*/
UPDATE threads
set
(

messages,

l_message_id, 
l_dateadded,
l_message_order,
l_message_date, 
l_user_id,
l_user_name

)
=
(

select
messages,

l_message_id, 
l_dateadded,
l_message_order,
l_message_date, 
l_user_id,
l_user_name

FROM threads_update
WHERE threads.thread_id = threads_update.thread_id );

the view is
CREATE or replace VIEW threads_update
as
select

threads.category_id,
threads.forum_id,
threads.thread_id,

(select count(message_id) from messages WHERE messages.thread_id =
threads.thread_id) messages,
(select max(message_id) from messages where messages.thread_id =
threads.thread_id) l_message_id,

messages.message_order l_message_order,
messages.dateadded l_message_date,
messages.thread l_thread,
messages.user_id l_user_id,

users.user_name l_user_name
from threads
left outer join messages on (messages.message_id = l_message_id) left outer join users on (users.user_id = l_user_id) order by forum_id, l_message_id; Received on Thu Jan 24 2002 - 17:36:07 CET