I want to update member_id,duration,pcses_id in table1 using values in table2 for a date range (table1.event_date) spanning +/- 5 mins of table2 (start_date). If we get multiple rows in table2 we need to take the latest time. Here is the query i have, which gives an error sub-query gives too many rows. Please let me know how can I accomplish this.

Forget about the update for now. Try to write a select for the condition you have described. Then we will help you how to convert it into an equivalent update statement. By doing it that way you learn more.

I came up with the following. What do you think, am I doing it right or do you have any better suggestions.

UPDATE aude.rb_table1 est
SET (member_id,duration,pcses_id)=(select max(pcs.member_id) keep (dense_rank first order by pcs.start_time) member_id,
max(pcs.duration) keep (dense_rank first order by pcs.start_time,pcs.member_id) duration,
max(pcs.pcses_id) keep (dense_rank first order by pcs.start_time,pcs.member_id,pcs.duration) ses_id
FROM (select p.*
from aude.rb_table2 p
where CAST (p.start_time AS DATE) >= to_date('01-AUG-07')
AND CAST (p.end_time AS DATE) < to_date('01-SEP-07')
) pcs
WHERE est.computer_id=pcs.computer_id
AND est.rn_id=pcs.meter_id
AND est.event_date BETWEEN CAST (pcs.start_time AS DATE) - 300/(24*60*60)
AND CAST (pcs.start_time AS DATE) + 300/(24*60*60)
AND pcs.xflag=0
group by est.rn_id,est.computer_id
);

It is always good practice to add the clause "Where" clausea with Update statement unless you want to update all the rows in the table nad unless you are expecting the fields to be updated with NULL in case Update subquery is not satisfied.

What he is trying to say it is a good practice to include the format when using to_date. Assuming you are writing a program you know what the program is supposed to do and you know what the requirement is, but still most of the organisation will ask you to write a specification. It is because if somebody is been asked to do an enhacement he/she can understand what the requirement is and what needs to be done. Similarly it is always a good practice to tell oracle what the format of the date is.