update probs

[sammybee]

Hi All,

Below is my script - can you see any obvious errors, its to update 4000 records I left it running for 2 hours - it should only take 20 mins max:-

update sb_void_losses
set sb_tot_rentloss = (Select sum(vtr_dr) from
void_transactions v1
where v1.vtr_pro_refno = sb_pro_refno
and vtr_effective_date between sb_void_date and sb_let_date
group by v1.vtr_pro_refno)

any ideas appreciated.

Many thanks

Sam

 

[sem]

What is that group by clause for? Do you have an index on vtr_pro_refno? Does sb_void_losses really contain only 4000 records?

Regards, Dima

http://seminihin.narod.ru

 

[sammybee]

Hi Dima,

The group by clause is because there my be more than 1 row per vtr_pro_refno in the void transactions table and I need a result for all rows.

There is no index on the vtr_pro_refno the table is indexed on a different field.

Sb_void_losses contains only contains 3408 rows.

Any feedback appreciated.

Many thanks

Sam

 

[Turkbear]

Hi,
What happens if you just run the subquery:

Select sum(vtr_dr) from void_transactions v1
where v1.vtr_pro_refno = sb_pro_refno
and vtr_effective_date between sb_void_date and sb_let_date
group by v1.vtr_pro_refno

 

[lewisp]

You wont get more than one row per vtr_pro_refno because you are restricting it with [tt]where v1.vtr_pro_refno = sb_pro_refno[/tt]. You dont need the group by.

If you did get more than one row back the SQL would fail with 'more than one row returned in single-row subquery'.

 

[sammybee]

Thanks for your help,

the sql ran for nearly 3 hours and worked successfully - just can't believe it took so long;-)

Sam