x

Please can somebody explain how quirky updates work?

I have been reading through Robyn Page's SQL Server Cursor Workbench and she has an example of a "quirky update" like so...

Create tables used.

CREATE TABLE #cb (cb_ID INT IDENTITY(1,1),--sequence of entries 1..n
    Et VARCHAR(10), --entryType
    amount money)--quantity

INSERT INTO #cb(et,amount) SELECT 'balance',465.00
INSERT INTO #cb(et,amount) SELECT 'sale',56.00
INSERT INTO #cb(et,amount) SELECT 'sale',434.30
INSERT INTO #cb(et,amount) SELECT 'purchase',20.04
INSERT INTO #cb(et,amount) SELECT 'purchase',65.00
INSERT INTO #cb(et,amount) SELECT 'sale',23.22
INSERT INTO #cb(et,amount) SELECT 'sale',45.80
INSERT INTO #cb(et,amount) SELECT 'purchase',34.08
INSERT INTO #cb(et,amount) SELECT 'purchase',78.30
INSERT INTO #cb(et,amount) SELECT 'purchase',56.00
INSERT INTO #cb(et,amount) SELECT 'sale',75.22
INSERT INTO #cb(et,amount) SELECT 'sale',5.80
INSERT INTO #cb(et,amount) SELECT 'purchase',3.08
INSERT INTO #cb(et,amount) SELECT 'sale',3.29
INSERT INTO #cb(et,amount) SELECT 'sale',100.80
INSERT INTO #cb(et,amount) SELECT 'sale',100.22
INSERT INTO #cb(et,amount) SELECT 'sale',23.80

DECLARE @cb TABLE(cb_ID INT,--sequence of entries 1..n
    Et VARCHAR(10), --entryType
    amount money,--quantity
    total money)

DECLARE @total money 

SET @total = 0

Now for the quirky update

INSERT INTO @cb(cb_id,Et,amount,total) 
SELECT cb_id,Et,CASE WHEN Et='purchase' 
    THEN -amount 
    ELSE amount 
    END,0 FROM #cb

UPDATE @cb 
SET @total = total = @total + amount FROM @cb 

SELECT [Entry Type]=Et, [amount]=amount, 
    [balance after transaction]=total FROM @cb ORDER BY cb_id

Now why is that a better way of doing things than just taking the original #cb table and doing an inner join like so?

SELECT [Entry Type] = MIN(#cb.Et),  [amount] = MIN(#cb.amount),  [balance after transaction] =  SUM(CASE WHEN total.Et = 'purchase'  THEN -total.amount  ELSE total.amount END)

FROM #cb total INNER JOIN #cb ON total.cb_id <= #cb.cb_id

GROUP BY #cb.cb_id ORDER BY #cb.cb_id
more ▼

asked Feb 17, 2010 at 10:07 AM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

It is better because it is faster by several orders of magnitude. Just look at the data, and the graph at the end of the section http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-cursor-workbench/#fourth And you'll see that the triangular join was so slow we couldn't even perform the test when there was a decent number of rows. It doesn't scale
Oct 11, 2010 at 01:40 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

(I tried to make this a comment to Jeff's answer, but it is too long)

Jeff makes good points, and there are times when a quirky update is appropriate. As he mentioned, it tends to be faster than most other solutions and uses only T-SQL.

But I counsel caution before using it. Remember that even if you are fully comfortable with "Black Arts" coding, in many organizations the person who maintains the code you write is likely to not be you, or at least not always you for the whole time it is in production. The next person probably will not understand it fully, so if you do use it, I suggest commenting it excessively.

Also, remember this is not part of the SQL Standard. This means nothing if you are using SQL Server only, but I try to use standards compliant code unless there is a good reason not to.

This is a perfectly valid technique that will work consistently if you write it correctly, and is very unlikely to be changed in a mere service pack (and fairly unlikely to be changed in any major release in the near future). Still, it is one that is hard for many people to understand and it is not standards compliant, and those are reasons to judge carefully if it is worth using.

more ▼

answered Feb 22, 2010 at 06:16 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x985
x129

asked: Feb 17, 2010 at 10:07 AM

Seen: 4574 times

Last Updated: Feb 17, 2010 at 10:07 AM