Update Multiple Values in a SQL query based on row values-CodePudding

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.

CodePudding user response：

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.