SQL Server : how to loop through a table applying multi-record updates to another?

Question

I am looking to update records in a table X, based on a table Y containing details of the updates. The difficulties are that

One row of Y represents an update to a specified number of records in X.
There may be multiple records in Y that specify different updates to records in X that are alike in the field by which they are matched; in this case, the updates should be applied to disjoint subsets of X.

Suppose X = materials(id, type_id, status, data); Y = material_updates(run_id, type_id, quantity, data)

(id is just an internal primary key field)

Then what I'd like to do is (the equivalent of) to loop through a simple query like

SELECT * 
FROM material_updates 
WHERE run_id = :run;

and for each row of the result set, apply something like

UPDATE TOP(row.quantity) materials 
SET data = row.data, status = 1
WHERE status = 0 AND type_id = row.type_id;

(the change to status happens to be constant in the problem I am trying to solve)

Sample data

materials_update table:

run_id  type_id quantity    data
   1       1        3         42
   1       2        2         69
   1       2        1        105

materials table before the update:

type_id status  data
   1       1     17
   1       1     17
   1       0      0
   1       0      0
   1       0      0
   1       0      0
   2       0      0
   2       0      0
   2       0      0
   2       0      0

materials table after the update:

type_id status  data
   1       1     17
   1       1     17
   1       1     42
   1       1     42
   1       1     42
   1       0      0
   2       1     69
   2       1     69
   2       1    105
   2       0      0

I think it can be done using a cursor, but is this the best solution, or is there a more efficient way?

The problem may appear very clear cut to you but we don't understand much of each. Can you give a few examples? — Code Different, Jan 12 '15 at 15:00
@ZoffDino Do you mean examples of a material_updates table and what the materials table should look like before and after the operation? — Stewart, Jan 12 '15 at 15:06
Looping is rarely needed in SQL. You can create an UPDATE statement with a `FROM` clause that pulls the required data from the other table, as well as join the source and target tables on common columns — Panagiotis Kanavos, Jan 12 '15 at 15:07
possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Panagiotis Kanavos, Jan 12 '15 at 15:08
@Stewart yes! Something that clearly demonstrate what your requirements are — Code Different, Jan 12 '15 at 15:08
@PanagiotisKanavos On a quick look, that other question seems to me to be a case where there's a one-to-one correspondence between records of the updates table and records of the table to be updated, which isn't the case here. — Stewart, Jan 12 '15 at 15:10
@Stewart no, that's not the case. The same rules apply as with any `WHERE` or `JOIN` statement - whichever record matches the expression is updated. — Panagiotis Kanavos, Jan 12 '15 at 15:17
@PanagiotisKanavos I don't follow you - how do the solutions given there enable me to control the number of matching records to update? — Stewart, Jan 12 '15 at 15:23
That's a different question. Your original question focused on "looping" which isn't necessary. How would you create a SELECT query that *returned* the "top quantity" in the first place without looping? It's possible using window functions, CTEs or subqueries but it's not trivial. Once you have the SELECT statement you can create the UPDATE as shown — Panagiotis Kanavos, Jan 12 '15 at 15:25
@Stewart can you explain the results that appear in 'materials table after the update'? — Donal, Jan 12 '15 at 15:32
Are there not more columns that define some sort of ordering? At a *guess*, we apply updates in order using the lowest `data` value and a `status` of `0` in the `materials` table - but how do we know that we should use up the two `69`s from `material_update` before processing the `105`? Is that also lowest to highest on `data` value? — Damien_The_Unbeliever, Jan 12 '15 at 15:42
@Donal The first record of material_updates means "update 3 materials with type_id 1 to data 42". Likewise with the other two. — Stewart, Jan 12 '15 at 16:42
Have a look at [this](http://stackoverflow.com/questions/9420173/sql-subtracting-a-depleting-value-from-rows) question. — HABO, Jan 12 '15 at 16:44
@Damien_The_Unbeliever The initial `data` values I had are just dummy values. I'll zero them to avoid confusion. I think that those records that haven't been updated yet should be all the same except for the `type_id`, and so it doesn't matter which individual records are updated. — Stewart, Jan 12 '15 at 16:46
"doesn't matter" is actually difficult to implement. It's far *easier* if we can have some determinism (because then we may be able to construct a solution that applies all updates in parallel, as a set-based operation, which is what SQL is *good* at) — Damien_The_Unbeliever, Jan 12 '15 at 17:33
The materials table has a internal, numeric primary key field - is this good enough? (I left it off previously in trying to simplify the problem.) — Stewart, Jan 12 '15 at 17:44

score 5 · Accepted Answer · edited May 17 '20 at 12:54

This is perfect for a CURSOR (msdn link), which allows you to iterate over the results of a query row-by-row and perform operations for each one.

This one here is a good tutorial about it.

Your need would be solved by this piece of code:

-- the best fit for this code would be a Stored Procedure with one parameter
-- which is the run_id value you want.

-- error checking omitted for brevity

DECLARE CURSOR theCursor 
FOR SELECT type_id, quantity, data FROM material_updates WHERE run_id = @run_id;

DECLARE @type_id int; -- types should match your material_updates fields
DECLARE @quantity int;
DECLARE @data int;

OPEN theCursor;

FETCH NEXT FROM theCursor INTO @type_id, @quantity, @data;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE TOP(@quantity) materials
    SET data = @data, status = 1
    WHERE status = 0 AND type_id = @type_id;
END;

CLOSE theCursor;
DEALLOCATE theCursor;

~~Another solution would be using UPDATE FROM (SO already has info about it) but I'm not aware of a way to make it update a specific quantity of rows.~~ It most likely can't do this.

Beware though that the data you're going to end up with makes no sense, because there is no ORDER: you'll never know which rows will be/have been updated.

There's no reason to use cursors for such a simple query - cursors are rarely needed but they do result in awful performance — Panagiotis Kanavos, Jan 12 '15 at 15:16
OP wants to update a precise number of rows, I can't think of an UPDATE FROM syntax which would work — Alex, Jan 12 '15 at 15:24
The trick is to limit the selected rows, as Donal already posted — Panagiotis Kanavos, Jan 12 '15 at 15:26

SQL Server : how to loop through a table applying multi-record updates to another?

1 Answers1