Is there a possible way only use SQL Update query to update July Data to August data? E.g. The Value of B in July should be updated to 12. Dimension Key is the primary key and Name is the "durable" key. CURSOR is allowed to use but not preferred.
1 Answer
You must join the table to itself to set different records of it in relation.
UPDATE A
SET A.Value = B.Value
FROM
mytable A
INNER JOIN mytable B
ON A.Name = B.Name
WHERE
A.Date = '2022-07-01' AND
B.Date = '2022-08-01'
If you want to do this for corresponding days of the whole month you change the query like this
UPDATE A
SET A.Value = B.Value
FROM
mytable A
INNER JOIN mytable B
ON A.Name = B.Name AND DAY(A.Date) = DAY(B.Date)
WHERE
YEAR(A.Date) = 2022 AND MONTH(A.Date) = 7 AND
YEAR(B.Date) = 2022 AND MONTH(B.Date) = 8
for a whole year you would write
...
ON A.Name = B.Name AND
DAY(A.Date) = DAY(B.Date) AND MONTH(A.Date) = MONTH(B.Date) - 1
WHERE
YEAR(A.Date) = 2022 AND
YEAR(B.Date) = 2022
for all the years:
...
ON A.Name = B.Name AND
DAY(A.Date) = DAY(B.Date) AND
MONTH(A.Date) = MONTH(B.Date) - 1 AND
YEAR(A.Date) = YEAR(B.Date)
However, this simple approach does not work if the months are in different years. And also the months have a different number of days. Since I do not know your real use case I am not going into more detail here.
Instead of writing SET A.Value = B.Value you can simply write SET Value = B.Value since the target table is specified in UPDATE A.
answered Aug 16, 2022 at 13:08
Olivier Jacot-Descombes
114k14 gold badges149 silver badges202 bronze badges
2 Comments
SeanZhang1997
Hi this is awesome and appreciated. In this way all the values in July will be updated. For performance purpose, adding A.Value!=B.Value in the WHERE clause would solve that part?
Olivier Jacot-Descombes
The answer is not that simple. See: UPDATE performance where no data changes.
UPDATEwith aJOIN. There is no need for aCURSORhere.