How to update records in groups of 5 sequentially

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hello,

A date field in a table has to be updated in groups of fives after the the first group is update the
date will be incremented by 1 and the next 5 records will be updated, till the whole records in the table
will be exhausted.

How do I accomplish this in :: MS SQL SERVER using the QUERY

start date '14/01/2018'

IDNO NAME DATE_CHG
--------------------------
001 AMY 14/01/2018
002 TET 14/01/2018
003 YAL 14/01/2018
004 PET 14/01/2018
005 JON 14/01/2018

0011 PAM 15/01/2018
0022 SETH 15/01/2018
0033 YALE 15/01/2018
0044 ANDY 15/01/2018
0055 JOHN 15/01/2018

Thanks

What I have tried:

This is an ongoing development looked at earlier codes

Posted 20-Jan-18 19:42pm

Member 12770648

Updated 23-Jan-18 7:48am

Add a Solution

Comments

David_Wimbley 21-Jan-18 2:03am

Why do you need to only update in groups of 5's. Seems in efficient to me, SQL will optimize the query accordingly and you can also look into indexes on your tables to see whether or not you need to add/remove indexes.

The second question is, how do you determine what 5 get updated in a group? Are you just trying to update all records with name "YAL"? Or are you just wanting to update it 5 at a time regardless of criteria...meaning 5 at a time with how they come out of the table. Meaning you could be updating id's 11, 3, 2, 1, 55.

In my opinion i think you either need to clarify what you are doing or rethink what you want to accomplish.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-01-20T20:09:00

It's going to be tricky, as you can't directly use GROUP BY in an UPDATE statement (UPDATE doesn't allow aggregate functions.
But... you could use A JOIN to a SELECT...GROUP BY but even then, it's going to be complicated, because you need the SELECT to return the row ID values together with a number you can use in the ADDDAYS function to generate the date code.
The last bit is relatively easy: ROW_NUMBER (Transact-SQL) | Microsoft Docs[^] will give you that if you use modulus 5 with it.
But it's going to be complicated to work out, and complicated to maintain and modify.

I'd be tempted to say this should be done in presentation software, rather than in SQL purely so it's easier to read!

Richard Deeming · Answer 2 · 2018-01-23T07:47:00

Something like this should work:

SQL

DECLARE @StartDate date = '20180114';

WITH cte As
(
    SELECT
        IDNO,
        (ROW_NUMBER() OVER (ORDER BY IDNO) - 1) / 5 As DaysToAdd
    FROM
        YourTable
)
UPDATE
    T
SET
    DATE_CHG = DateAdd(day, G.DaysToAdd, @StartDate)
FROM
    YourTable As T
    INNER JOIN cte As G
    ON G.IDNO = T.IDNO
;

How to update records in groups of 5 sequentially

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0