Страницы

Поиск по вопросам

вторник, 21 мая 2019 г.

Интервал с определенным шагом в MS SQL

Есть ли возможность указать шаг в интервале при вставке множества значений в таблицу с помощью UPDATE в MS SQL? Например, хотелось бы обновить значения в таблице, где ID будет в интервале от 100 до 500 с шагом 30. Либо в качестве альтернативы как можно обойтись без интервала, чтоб обновление происходило в цикле в определенном промежутке с конкретным шагом? Ниже укажу пример кода, в котором хотелось бы реализовать это, если возможно.
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;


Ответ

Пока так, навскидку:
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;

Комментариев нет:

Отправить комментарий