CODEWITHSUNDEEP
sqlsql-servert-sqlvariablessql-update
Joshua Rajandiran
Joshua Rajandiran

Reputation: 2928

Having trouble understanding this query

Basically I can't understand what this query below does:

UPDATE @so_stockmove 
SET  @total_move_qty = total_move_qty = (
          CASE WHEN @so_docdt_id <> so_docdt_id THEN 0 
          ELSE ISNULL(@total_move_qty, 0) 
          END
          ) + ISNULL(move_qty,0),
     balance = so_qty - @total_move_qty,
     @so_docdt_id = so_docdt_id

I only can guess that it updates each row for the columns total_move_qty,balance,so_docdt_id.

Can someone explain to me in detail what the query means:

UPDATE tbl SET @variable1 = columnA = expression

Upvotes: 0

Views: 63

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82534

Update
After reading @MotoGP comments, I did some digging and found this article by Jeff Moden where he states the following:

Warning:
Well, sort of. Lots of folks (including some of the "big" names in the SQL world) warn against and, sometimes, outright condemn the method contained in this article as "unreliable" & "unsupported". One fellow MVP even called it an "undocumented hack" on the fairly recent "24 hours of SQL". Even the very core of the method, the ability to update a variable from row to row, has been cursed in a similar fashion. Worse yet, except for the ability to do 3 part updates (SET @variable = columnname = expression) and to update both variables and columns at the same time, there is absolutely no Microsoft documentation to support the use of this method in any way, shape, or form. In fact, even Microsoft has stated that there is no guarantee that this method will work correctly all the time.

Now, let me tell you that, except for one thing, that's ALL true. The one thing that isn't true is its alleged unreliability. That's part of the goal of the article... to prove its reliability (which really can't be done unless you use it. It's like proving the reliability of the SELECT statement). At the end of the article, make up your own mind. If you decide that you don't want to use such a very old ,yet, undocumented feature, then use a Cursor or While loop or maybe even a CLR because all of the other methods are just too darned slow. Heh... just stop telling me that it's an undocumented hack... I already know that and, now, so do you. ;-)

First edition

Well, this query updates columns total_move_qty and balance in a table variable called @so_stockmove, and in the same time sets values to the variables called @total_move_qty and @so_docdt_id.

I didn't know it's possible to assign values to more then one target this way in Sql server (@variable1 = columnA = expression) but apparently that is possible.

Here is my test:

declare @bla char(1)

declare @tbl table
(
    X   char(1)
)

insert into @tbl VALUES ('A'),('B'), ('C')

SELECT *
FROM @tbl

UPDATE @tbl 
SET @Bla = X = 'D'

SELECT *
FROM @tbl

SELECT @bla

Results:

X -- first select before update
----
A
B
C


X -- second select after update
----
D
D
D

---- select the variable value after update
D

Upvotes: 2

Li0liQ
Li0liQ

Reputation: 11264

It just sets the value to the variable and updates the field.

Upvotes: 0

Related Questions