New to SQL Analytics and need help

[Scott Johnson]

I'm fairly new to sql analytics and need help with an relatively simple
problem.

Given table:
ID Operation Date Old_Loc New_Loc
100 9/1/04 12:34 B1
100 9/1/04 12:45 B2
100 9/1/04 13:30


I want to move the location down by one record so the result will be
ID Operation Date Old_Loc New_Loc
100 9/1/04 12:34 B1
100 9/1/04 12:45 B2 B1
100 9/1/04 13:30 B2


I'm using:

update tab_name
set new_loc = (select lead(old_loc) over (partition by id order by
operation_dt) from tab_name);

but I'm getting Ora 1427 - Single-row subquery returns more than one row.

Like I said, I'm new to sql analytics so I may be completely off base.

Can anyone offer some insight?

Thanks,
Scott

[Pratap]

You have to lead LAG instead of LEAD

To update, if you have few rows then this will work fine -

begin
for rec in
(
select lead(old_loc) over (partition by id order by operation_dt)
a, rowid rid
from tab_name
)
loop
update tab_name set new_loc = rec.a where rowid = rid
end loop;
end;

But if you have too many rows and performance is an issue then use
merge command as given here -

http://groups.google.co.in/groups?hl=en&lr=&ie=UTF-8&threadm=830861d2.0409230400.3a1ea0cd%40posting.google.com&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26group%3Dcomp.databases.oracle.server

Regards,

Pratap