Is it possible to specify a step in the interval when inserting a set of values ​​into a table using UPDATE in MS SQL? For example, I would like to update the values ​​in the table, where the ID will be in the range from 100 to 500 in increments of 30. Or, alternatively, how can we manage without the interval so that the update takes place in a loop in a certain interval with a specific step? Below I will indicate an example of code in which I would like to implement it, if possible.

 UPDATE Rates SET Amount = ( SELECT Amount FROM ( SELECT KK.Amount, KK.OperationName FROM ( SELECT R.Col1, R.RateID, JJ.Type, FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount, JJ.OperationName, JJ.Currency FROM Rates R LEFT JOIN ( SELECT * FROM OriginTransacts OT WHERE OT.Period = 'Month' AND OT.OperationName = 'Clothes Shopping' ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10) ) AS KK ) AS LL WHERE Amount IS NOT NULL ), OperationName = ( SELECT OperationName FROM ( SELECT KK.Amount, KK.OperationName FROM ( SELECT R.Col1, R.RateID, JJ.Type, FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount, JJ.OperationName, JJ.Currency FROM Rates R LEFT JOIN ( SELECT * FROM OriginTransacts OT WHERE OT.Period = 'Month' AND OT.OperationName = 'Clothes Shopping' ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10) ) AS KK ) AS LL WHERE Amount IS NOT NULL ) WHERE Rates.RateID IN(FLOOR(RAND() * 5 + 1110), FLOOR(RAND() * 5 + 1140), FLOOR(RAND() * 5 + 1170), FLOOR(RAND() * 5 + 1200), FLOOR(RAND() * 5 + 1230), FLOOR(RAND() * 5 + 1260), FLOOR(RAND() * 5 + 1290), FLOOR(RAND() * 5 + 1320), FLOOR(RAND() * 5 + 1350), FLOOR(RAND() * 5 + 1380), FLOOR(RAND() * 5 + 1410), FLOOR(RAND() * 5 + 1440)) AND Rates.Amount IS NULL;
  • the cursor do not consider? - Anatol
  • Honestly, while completely new to this business. I will consider if you tell me how it can be used in my example. - Daniyal Lukmanov
  • or WHILE with an increment of +30 - Anatol
  • C WHILE tried, but did not work. Either I wrote the wrong script, or it does not work. - Daniyal Lukmanov
  • You have to update two fields using subqueries that are identical - the only difference is in the returned field, which in my opinion is not very optimal. You can make a subquery in a JOIN. - Vasil Baymurzin

2 answers 2

So far, offhand:

 DECLARE @i INT= 100; WHILE @i <= 500 BEGIN UPDATE Rates SET Amount = ( SELECT Amount FROM ( SELECT KK.Amount, KK.OperationName FROM ( SELECT R.Col1, R.RateID, JJ.Type, FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount, JJ.OperationName, JJ.Currency FROM Rates R LEFT JOIN ( SELECT * FROM OriginTransacts OT WHERE OT.Period = 'Month' AND OT.OperationName = 'Clothes Shopping' ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10) ) AS KK ) AS LL WHERE Amount IS NOT NULL ), OperationName = ( SELECT OperationName FROM ( SELECT KK.Amount, KK.OperationName FROM ( SELECT R.Col1, R.RateID, JJ.Type, FLOOR(JJ.AmountMin + RAND() * (JJ.AmountMax + 1 - JJ.AmountMin)) AS Amount, JJ.OperationName, JJ.Currency FROM Rates R LEFT JOIN ( SELECT * FROM OriginTransacts OT WHERE OT.Period = 'Month' AND OT.OperationName = 'Clothes Shopping' ) AS JJ ON R.RateID = FLOOR(RAND() * 5 + 10) ) AS KK ) AS LL WHERE Amount IS NOT NULL ) WHERE Rates.RateID = @i AND Rates.Amount IS NULL; SET @i+=30; END;
  • And the initial value of the interval can be specified immediately after BEGIN as SET @i=300 , for example? - Daniyal Lukmanov
  • the initial value is indicated at the very beginning, when the variable @i is @i - Anatol
  • Yes, it works. Thank you. For some reason, I had an error popping up about the need to declare a variable, although I kind of did it too. - Daniyal Lukmanov
  • 3
    @DaniyalLukmanov In general, the cycle is completely unnecessary, it is enough just conditions where Rates.RateID between 100 and 500 AND Rates.RateID % 30 = 0 - Mike
  • that's right - Anatol

I think it is necessary to first generate a number of identifiers with the necessary step and then apply it when updating. Example:

 DECLARE @BeginID INT = 100 - the initial value of the identifier
   , @EndID INT = 500 - the final value of the identifier
   , @Step INT = 30;  - step

 WITH CTE (id)
 AS (
   SELECT @BeginID

   UNION ALL

   SELECT id + @Step
   FROM CTE
   WHERE id + @Step <= @EndID
   )

 UPDATE Rates SET
   - your field update code
 FROM CTE
 WHERE Rates.RateID = CTE.ID
 OPTION (MAXRECURSION 0)