Есть ли возможность указать шаг в интервале при вставке множества значений в таблицу с помощью 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;
Комментариев нет:
Отправить комментарий