Cursor with Temp Table & Update

Question

Cursor with Temp Table & Update

VA123

SSCarpal Tunnel

Points: 4272
More actions
July 18, 2008 at 9:05 am

#184903

I have a cursor defined as such:
Declare GEProfit Cursor For
Select ILINVN
, ILODPX
, ILORD
, SITYPE
, ILDOCN
, ISCST
, HEDTE
, PECST
, ILQTY
, ILREV
, ILLINE
, ILPROD
From #GEJobProfit
Order By SIREG
, ILINVN
, ILORD
, ILLINE;
I then update a row in the temp table using this code:
Update #GEJobProfit
Set COST = @ILQTY * @EST_COST
Where Current Of GEProfit;
I get an error complaining that the cursor is not updatable. The code worked when I used a permanent table. Are Temp tables updatable when using 'Where Current of"?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

d_sysuk SSC Eights! Points: 809 More actions · Answer 1

Why use a cursor??

If you have a quick look in BOL, you can perform this without any cursor.. [UPDATE (T-SQL)]. There is an ability to link the current record you are working on , and update the source table - bits in bold important bit here.

The following example modifies the SalesYTD column in the SalesPerson table to reflect the most recent sales recorded in the SalesOrderHeader table.

USE AdventureWorks;

GO

UPDATE Sales.SalesPerson

SET SalesYTD = SalesYTD + SubTotal

FROM Sales.SalesPerson AS sp

JOIN Sales.SalesOrderHeader AS so

ON sp.SalesPersonID = so.SalesPersonID

AND so.OrderDate = (SELECT MAX(OrderDate)

FROM Sales.SalesOrderHeader

WHERE SalesPersonID =

sp.SalesPersonID);

GO

So this would just mean paracuting your query in...

Set g1.COST = g2.ILQTY * @EST_COST

from #gejobprofit g1

join ( select ILODPX

, ILORD

, SITYPE

, ILDOCN

, ISCST

, HEDTE

, PECST

, ILQTY

, ILREV

, ILLINE

, ILPROD

From #GEJobProfit

Order By SIREG

, ILINVN

, ILORD

, ILLINE) g2

on ::: ADD JOIN CRITERIA HERE :::

-- may be the PK criteria

-- Need some more info on table structure of '#GEJobProfit'

Self-referencing table joins are supported in SQL Server 2005, making updates to the current row possible whilst reading data from other sources linking on the PK or whatever field is the link for update

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 2

Thanks d_sysuk.

There is a significant amount of code that is executed to determine the value of the variable @EST_COST. That's why I'm using a cursor.

Are temp tables updatable in a cursor?

d_sysuk SSC Eights! Points: 809 More actions · Answer 3

Even if there is a lot of cost involved in estimating that variable, I will guarantee the non-cursor solution will run significantly faster.

If the @est var is hard to calculate, populate to a temp table with any identifying key id, then perform a join up at the last stage- so you have a set based update.

A single row based update will absolutly kill performance for you. I'm one of those people who will never use cursors, and have always refactored other peoples work to remove the cursors from code - resulting in significantly better performance.

mansour.bozorgmehr Grasshopper Points: 15 More actions · Answer 4

I had this problem. I added a PRIMARY KEY and IDENTITY (1, 1) to my Temp Table. My problem solved and I am already able to Update directly Cursor without any problem

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

mansour.bozorgmehr - Monday, January 15, 2018 6:22 AM
I had this problem. I added a PRIMARY KEY and IDENTITY (1, 1) to my Temp Table. My problem solved and I am already able to Update directly Cursor without any problem

If the end result was that you still used a cursor, then you may still have a problem. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)