Assigning max value + 1 to a column

Question

Assigning max value + 1 to a column

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

TheSQLGuru (5/20/2016)
Luis Cazares (5/20/2016)
This should fix the concurrency problems, and a few pet peeves I have.
WITH cte AS (
SELECT o.*,
ISNULL((SELECT MAX(o.InvoiceNo) FROM Orders o),0) + ROW_NUMBER() OVER (ORDER BY o.ID ASC) NewInvoiceNo
FROMOrders o
WHERE o.DeliveryDate >= '20160101'
AND o.DeliveryDate <= '20160131'
AND EXISTS( SELECT 1
FROM SetupAccountTypes a
WHERE a.ID = o.AccountTypeID
AND a.AccountType = 'COD')
)
UPDATE cte
SET InvoiceNo = cte.NewInvoiceNo;
Having a brain cloud at the moment but can't recall if this syntax will lead to that MAX getting iterated or just run once for all affected rows.

It'll just run once.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

yahya-236450 Old Hand Points: 369 More actions · Answer 2

It'll just run once.

Does that mean all the orders will get the same invoice numbers?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

No. The MAX will get the highest existing one and then the ROW_NUMBER will add a sequence from 1-N to that as an increment.

I asked my question because this SELECT in SELECT type of statement can often lead to very ugly loopy plans.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service