Mixing
MSSQL: How to increment a int-column grouped by another column?
0
Given the following table:
UserId | Idx
1 | 0
1 | 1
1 | 3
1 | 5
2 | 1
2 | 2
2 | 3
2 | 5
And I want to update the Idx column that it is correctly incremented grouped by UserId column:
UserId | Idx
1 | 0
1 | 1
1 | 2
1 | 3
2 | 0
2 | 1
2 | 2
2 | 3
I know its possible with T-SQL (with Cursor), but is it also possible with a single statement?
Thank you
sql
sql-server asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
add a comment |
0
Given the following table:
UserId | Idx
1 | 0
1 | 1
1 | 3
1 | 5
2 | 1
2 | 2
2 | 3
2 | 5
And I want to update the Idx column that it is correctly incremented grouped by UserId column:
UserId | Idx
1 | 0
1 | 1
1 | 2
1 | 3
2 | 0
2 | 1
2 | 2
2 | 3
I know its possible with T-SQL (with Cursor), but is it also possible with a single statement?
Thank you
sql
sql-server asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
ROW_NUMBER (Transact-SQL) (A cursor is an awful way to update these values).
– Larnu
Nov 21 '18 at 9:41
1
DoIdxvalues get rearranged to control some form of presentation order? If so, trying to eliminate gaps in the stored form is often unnecessary - you can generate contiguous numbering duringSELECT.
– Damien_The_Unbeliever
Nov 21 '18 at 9:47
add a comment |
0
0
0
Given the following table:
UserId | Idx
1 | 0
1 | 1
1 | 3
1 | 5
2 | 1
2 | 2
2 | 3
2 | 5
And I want to update the Idx column that it is correctly incremented grouped by UserId column:
UserId | Idx
1 | 0
1 | 1
1 | 2
1 | 3
2 | 0
2 | 1
2 | 2
2 | 3
I know its possible with T-SQL (with Cursor), but is it also possible with a single statement?
Thank you
sql
sql-server asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
Given the following table:
UserId | Idx
1 | 0
1 | 1
1 | 3
1 | 5
2 | 1
2 | 2
2 | 3
2 | 5
And I want to update the Idx column that it is correctly incremented grouped by UserId column:
UserId | Idx
1 | 0
1 | 1
1 | 2
1 | 3
2 | 0
2 | 1
2 | 2
2 | 3
I know its possible with T-SQL (with Cursor), but is it also possible with a single statement?
Thank you
sql
sql-server sql
sql-server asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
asked Nov 21 '18 at 9:40
PowerslavePowerslave
13512
13512
ROW_NUMBER (Transact-SQL) (A cursor is an awful way to update these values).
– Larnu
Nov 21 '18 at 9:41
1
DoIdxvalues get rearranged to control some form of presentation order? If so, trying to eliminate gaps in the stored form is often unnecessary - you can generate contiguous numbering duringSELECT.
– Damien_The_Unbeliever
Nov 21 '18 at 9:47
add a comment |
ROW_NUMBER (Transact-SQL) (A cursor is an awful way to update these values).
– Larnu
Nov 21 '18 at 9:41
1
DoIdxvalues get rearranged to control some form of presentation order? If so, trying to eliminate gaps in the stored form is often unnecessary - you can generate contiguous numbering duringSELECT.
– Damien_The_Unbeliever
Nov 21 '18 at 9:47
ROW_NUMBER (Transact-SQL) (A cursor is an awful way to update these values).
– Larnu
Nov 21 '18 at 9:41
ROW_NUMBER (Transact-SQL) (A cursor is an awful way to update these values).
– Larnu
Nov 21 '18 at 9:41
1
1
Do
Idx values get rearranged to control some form of presentation order? If so, trying to eliminate gaps in the stored form is often unnecessary - you can generate contiguous numbering during SELECT.– Damien_The_Unbeliever
Nov 21 '18 at 9:47
Do
Idx values get rearranged to control some form of presentation order? If so, trying to eliminate gaps in the stored form is often unnecessary - you can generate contiguous numbering during SELECT.– Damien_The_Unbeliever
Nov 21 '18 at 9:47
add a comment |
3 Answers
3
active
oldest
votes
2
You can use correlated subquery :
update t
set idx = coalesce((select count(*)
from table as t1
where t1.userid = t.userid and t1.idx < t.idx
), 0
);
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
Thanks, that worked
– Powerslave
Nov 21 '18 at 10:13
@Powerslave . . . Although the logic works, this is definitely not the best approach in SQL Server
– Gordon Linoff
Nov 21 '18 at 11:51
@GordonLinoff. . . I have considered this as once becauseupdateis not daily running statement. So, that just fine for one or two time executions.
– Yogesh Sharma
Nov 21 '18 at 11:55
add a comment |
0
Use ROW_NUMBER() with Partition
update tablex set Idx=A.Idx
from tablex T
inner join
(
select UserID ,ID,ROW_NUMBER() OVER (PARTITION BY UserID ORDER By UserID)-1 Idx
from tablex
) A on T.ID=A.ID
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
add a comment |
0
Use an updatable CTE:
with toupdate as (
select t.*,
row_number() over (partition by user_id order by idx) - 1 as new_idx
from t
)
update toupdate
set idx = new_idx
where new_idx <> new_idx;
This should be the fastest method for solving this problem.
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409110%2fmssql-how-to-increment-a-int-column-grouped-by-another-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
You can use correlated subquery :
update t
set idx = coalesce((select count(*)
from table as t1
where t1.userid = t.userid and t1.idx < t.idx
), 0
);
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
Thanks, that worked
– Powerslave
Nov 21 '18 at 10:13
@Powerslave . . . Although the logic works, this is definitely not the best approach in SQL Server
– Gordon Linoff
Nov 21 '18 at 11:51
@GordonLinoff. . . I have considered this as once becauseupdateis not daily running statement. So, that just fine for one or two time executions.
– Yogesh Sharma
Nov 21 '18 at 11:55
add a comment |
2
You can use correlated subquery :
update t
set idx = coalesce((select count(*)
from table as t1
where t1.userid = t.userid and t1.idx < t.idx
), 0
);
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
Thanks, that worked
– Powerslave
Nov 21 '18 at 10:13
@Powerslave . . . Although the logic works, this is definitely not the best approach in SQL Server
– Gordon Linoff
Nov 21 '18 at 11:51
@GordonLinoff. . . I have considered this as once becauseupdateis not daily running statement. So, that just fine for one or two time executions.
– Yogesh Sharma
Nov 21 '18 at 11:55
add a comment |
2
2
2
You can use correlated subquery :
update t
set idx = coalesce((select count(*)
from table as t1
where t1.userid = t.userid and t1.idx < t.idx
), 0
);
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
You can use correlated subquery :
update t
set idx = coalesce((select count(*)
from table as t1
where t1.userid = t.userid and t1.idx < t.idx
), 0
);
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
answered Nov 21 '18 at 9:46
Yogesh SharmaYogesh Sharma
30.9k51437
30.9k51437
Thanks, that worked
– Powerslave
Nov 21 '18 at 10:13
@Powerslave . . . Although the logic works, this is definitely not the best approach in SQL Server
– Gordon Linoff
Nov 21 '18 at 11:51
@GordonLinoff. . . I have considered this as once becauseupdateis not daily running statement. So, that just fine for one or two time executions.
– Yogesh Sharma
Nov 21 '18 at 11:55
add a comment |
Thanks, that worked
– Powerslave
Nov 21 '18 at 10:13
@Powerslave . . . Although the logic works, this is definitely not the best approach in SQL Server
– Gordon Linoff
Nov 21 '18 at 11:51
@GordonLinoff. . . I have considered this as once becauseupdateis not daily running statement. So, that just fine for one or two time executions.
– Yogesh Sharma
Nov 21 '18 at 11:55
Thanks, that worked
– Powerslave
Nov 21 '18 at 10:13
Thanks, that worked
– Powerslave
Nov 21 '18 at 10:13
@Powerslave . . . Although the logic works, this is definitely not the best approach in SQL Server
– Gordon Linoff
Nov 21 '18 at 11:51
@Powerslave . . . Although the logic works, this is definitely not the best approach in SQL Server
– Gordon Linoff
Nov 21 '18 at 11:51
@GordonLinoff. . . I have considered this as once because
update is not daily running statement. So, that just fine for one or two time executions.– Yogesh Sharma
Nov 21 '18 at 11:55
@GordonLinoff. . . I have considered this as once because
update is not daily running statement. So, that just fine for one or two time executions.– Yogesh Sharma
Nov 21 '18 at 11:55
add a comment |
0
Use ROW_NUMBER() with Partition
update tablex set Idx=A.Idx
from tablex T
inner join
(
select UserID ,ID,ROW_NUMBER() OVER (PARTITION BY UserID ORDER By UserID)-1 Idx
from tablex
) A on T.ID=A.ID
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
add a comment |
0
Use ROW_NUMBER() with Partition
update tablex set Idx=A.Idx
from tablex T
inner join
(
select UserID ,ID,ROW_NUMBER() OVER (PARTITION BY UserID ORDER By UserID)-1 Idx
from tablex
) A on T.ID=A.ID
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
add a comment |
0
0
0
Use ROW_NUMBER() with Partition
update tablex set Idx=A.Idx
from tablex T
inner join
(
select UserID ,ID,ROW_NUMBER() OVER (PARTITION BY UserID ORDER By UserID)-1 Idx
from tablex
) A on T.ID=A.ID
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
Use ROW_NUMBER() with Partition
update tablex set Idx=A.Idx
from tablex T
inner join
(
select UserID ,ID,ROW_NUMBER() OVER (PARTITION BY UserID ORDER By UserID)-1 Idx
from tablex
) A on T.ID=A.ID
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
answered Nov 21 '18 at 11:15
CAGDAS AYDINCAGDAS AYDIN
5418
5418
add a comment |
add a comment |
0
Use an updatable CTE:
with toupdate as (
select t.*,
row_number() over (partition by user_id order by idx) - 1 as new_idx
from t
)
update toupdate
set idx = new_idx
where new_idx <> new_idx;
This should be the fastest method for solving this problem.
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
add a comment |
0
Use an updatable CTE:
with toupdate as (
select t.*,
row_number() over (partition by user_id order by idx) - 1 as new_idx
from t
)
update toupdate
set idx = new_idx
where new_idx <> new_idx;
This should be the fastest method for solving this problem.
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
add a comment |
0
0
0
Use an updatable CTE:
with toupdate as (
select t.*,
row_number() over (partition by user_id order by idx) - 1 as new_idx
from t
)
update toupdate
set idx = new_idx
where new_idx <> new_idx;
This should be the fastest method for solving this problem.
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
Use an updatable CTE:
with toupdate as (
select t.*,
row_number() over (partition by user_id order by idx) - 1 as new_idx
from t
)
update toupdate
set idx = new_idx
where new_idx <> new_idx;
This should be the fastest method for solving this problem.
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
answered Nov 21 '18 at 11:51
Gordon LinoffGordon Linoff
773k35306408
773k35306408
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53409110%2fmssql-how-to-increment-a-int-column-grouped-by-another-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
ROW_NUMBER (Transact-SQL) (A cursor is an awful way to update these values).
– Larnu
Nov 21 '18 at 9:41
1
Do
Idxvalues get rearranged to control some form of presentation order? If so, trying to eliminate gaps in the stored form is often unnecessary - you can generate contiguous numbering duringSELECT.– Damien_The_Unbeliever
Nov 21 '18 at 9:47