SQL UPDATE using unusual syntax

Question

I came across this stored procedure today and I'm confused with the syntax on it. It is doing an UPDATE and the query looks like this:

DECLARE @TransactionStatus tinyint = 1, 
        @RedeemedAmount decimal(18,2) = 0, 
        @PromotionCodeAmount decimal(18,2) = 0,
        @EventDetailId int = 0

UPDATE PED
    SET @RedeemedAmount = PED.RedeemedAmount = PED.RedeemedAmount + PE.PromoCodeAmount, 
        @PromotionCodeAmount = PE.PromoCodeAmount,   
        @EventDetailId = PED.Id
FROM dbo.PromotionEvents AS PE 
JOIN dbo.PromotionEventDetails AS PED ON PED.EventId = PE.Id
    WHERE PED.Id IN (
        SELECT TOP 1 PED.ID
        FROM dbo.PromotionEvents AS PE 
        JOIN dbo.PromotionEventDetails AS PED ON PED.EventId = PE.Id
        WHERE PE.Id = @EventId
    AND PED.Amount >= PED.RedeemedAmount + PE.PromoCodeAmount 
        AND PE.StartDate <= GETDATE() AND PE.EndDate > GETDATE()
        AND PE.MStatusId = 1
        AND PED.MStatusId = 1
        ORDER BY PED.CreatedDateTime)

This is only a portion of the whole stored procedure. I just need to understand what this part does?

SET @RedeemedAmount = PED.RedeemedAmount = PED.RedeemedAmount + PE.PromoCodeAmount,

Notice that there are two equals signs on that line.

This is just some algebra, adding three decimals together and storing that result to another decimal. — Tim Biegeleisen, Jun 04 '18 at 05:03
@TimBiegeleisen did you notice there is `=` not `+` between the decimals, i.e. two `=` signs on the line not two `+` signs? — Jiri Tousek, Jun 04 '18 at 05:06
Hey @TimBiegeleisen. Thanks for the prompt reply. I had the same thought too but when i googled, I just couldn't find any reference to this. Do you happen to have one? — Docker John, Jun 04 '18 at 05:08
@JiriTousek I missed that. I've never seen this in SQL. In Java, yes, but not in SQL. — Tim Biegeleisen, Jun 04 '18 at 05:09
This technique is a bit odd to say the least and there are very important caveats to this working correctly. It is known as the "quirky update". Jeff Moden has a great article on the topic here and how to use this for a running total. http://www.sqlservercentral.com/articles/T-SQL/68467/ — Sean Lange, Jun 04 '18 at 13:08

Jiri Tousek · Accepted Answer · 2018-06-04T05:25:39.393

2

That line does two things at once:

Updates the PromotionEventDetails.RedeemedAmount column
Stores the same value in @RedeemedAmount variable

This seems to be a mix of usual UPDATE ... SET column = expression with SQL Server's "set variable" syntax of SET @variable = expression. Tests show it is impossible to do @a = @b = col1 or col1 = col2 = col3, it only works for one variable and one target column.

edited Jun 04 '18 at 05:25

answered Jun 04 '18 at 05:13

Jiri Tousek

12,211
5
29
43

As for these 2 lines, i guess they are just setting up the variable aren't they? `@PromotionCodeAmount = PE.PromoCodeAmount, @EventDetailId = PED.Id` – Docker John Jun 04 '18 at 05:16

SQL UPDATE using unusual syntax

1 Answers1