Update query with increments 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update query with increments 1

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-04-29 : 16:01:44

How to increment "step" by increments of 1. starting with 1.

i have 5000 rows, there are multiple rows under each moduleid.
if there are 3 rows under moduleid=500
then sort by duedate and give the

first row STEP = 1
second row Step = 2
third row Step = 3 and so on.

Is it possible to do it with one update statement, where it has to group by moduleid? i have almost 5k rows within this table.

Update table_Orders set step = n+1 sort by duedate based on moduleid group by moduleID

Thank you very much for the helpful info.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 16:05:34

update tbl
set select step = (select count(*) from tbl t2 where t2.moduleid = t1.moduleid and t2.duedate <= t1.duedate)
from tbl t1

or
;with cte as
(
select moduleid, duedate, step = row_number() over (partition by moduleid order by duedate)
)
update tbl
set step = cte.seq
from tbl t
join cte
on cte.moduleid = t.moduleid
and cte.duedate = t.duedate

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-04-29 : 16:22:52

Thank you Nigel.
I tried to use the following,but after the set i see select right before step.
also can you please tell me should i ned to use sort by duedate? or this query will automatically sort.

Also when executed it put 2,2,3
i have 3 rows with moduleid = 202
it seems like it did'nt put 1,2,3

also i notice there are 11 rows all has step as 11.
Can you please help.

----------------
update tbl
set select step = (select count(*) from tbl t2 where t2.moduleid = t1.moduleid and t2.duedate <= t1.duedate)
from tbl t1
----------------

Thank you very much for the helpful info.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 16:32:35

It will "sort"
The subquery counts how many rows for that moduleid have an earlier (or same) duedate and updates to that value.
Note this assumes you don't have duplicate duedates for a moduleid.
Don't you have anything to test with? Create a temp table to test code before you run it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-04-29 : 16:36:24

I have a test data, where i executed the query, the problem is i do have records with same duedate.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-04-29 : 16:39:15

You are right, i have 13 rows with moduleid=187

out of that 11 rows has same duedate, now it put for all 11 rows:

step=11 .

step 1 = 1 row
step 2 = 1 row
step 11 = 11rows

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-04-29 : 16:42:42

Is it possible to use to use primary field ID for this child table.

ActID is the primary key ID on this table.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 16:48:53

What I have given will do that.
All that have the same duedate will have the same step - the count of all the duedates less than or equal to the row value.

The version using row_number won't work in this case.
would have to be

;with cte1 as
(
select moduleid, duedate, step = row_number() over (partition by moduleid order by duedate)
),
cte as
(
select moduleid, duedate, step = max(step) from cte1 group by moduleid, duedate
)
update tbl
set step = cte.step
from tbl t
join cte
on cte.moduleid = t.moduleid
and cte.duedate = t.duedate

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources