Help plz...No more spool space!

Database
Enthusiast

Help plz...No more spool space!

HI all!

The foll query gives me a no more spool space error...

collect stats on DB.tab1 col1;
update DB.tab1 from
(SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col2 order by col1 ) as SaleRank
FROM DB.tab2)t
set salerank = t.salerank;

Any help is highly appreciated.

TIA!
1 REPLY
Enthusiast

Re: Help plz...No more spool space!

You need to join your derived table t to tab1 on all the primary key values. (Note Primary KEY not primary index!)
So:

update DB.tab1 from
(SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col2 order by col1 ) as SaleRank
FROM DB.tab2)t
set salerank = t.salerank
Where DB.tab1.pk1 = t.col2;

(Assumes pk1 is the sole primary key value in tab1 (eg something like a branch code or salesman id) and tab2.col2 is the same.)