Alternative for thousands of updates

Question

Hi All,

I have a scenario where I want to update one table thousands of time inside a loop. This query need to check last updated value. So updating is necessary before the next transaction. 

For an example, query will update Col1 column. The updated Col1 value will be considered in next statement. So on.

Any suggestion for this with low cost will be highly appreciated. 

Answer 1

Post query & DDL. Thanks.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

Without knowing the details its difficult to arrive at a good solution. However I think a recursive CTE which will arrive at the final value and then one update to commit it would work.

---

Jayanth Kurup - www.enabledbusiness.com

Answer 3

Query:

Update table1

set Value=Value-@var

where id=[Some Value coming from while loop]

@var is the updated value in previous transaction.

I'm running this query inside following while loop.

While count(*) from table1

BEGIN

execute Query1

END

Answer 4

hi there, i think you should try it with cte. update

WITH Updates 

AS

(SELECT P.product_desc,

        P.price,

        U.product_desc AS new_product_desc,

        U.price AS new_price

 FROM Products AS P

 JOIN ProductUpdates AS U

   ON P.sku = U.sku

 WHERE U.effective_date < CURRENT_TIMESTAMP)

UPDATE Updates

SET product_desc = new_product_desc,

    price = new_price;

 

---

thx benedikt

Answer 5

Hi Jayanth,

Unfortunately I can't use recursive CTE since I have to invoke two functions based on sign of the value. update is only one function. Looping record set through sorted list. So only some rows redirect to update function.

Answer 6

That's not a recursive CTE, it's just a CTE, which is the preferred way to write an UPDATE statement involving a join of multiple tables.  You write a query that returns the rows you want updated, and the columns to update and the new values for those columns.

Then in a simple UPDATE set each column to the new value.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 7

The DDL and DML for the query would be really helpful at this point.

---

Jayanth Kurup - www.enabledbusiness.com