updating a column

Question

updating a column

BeginnerBug

Hall of Fame

Points: 3539
More actions
January 9, 2011 at 4:01 am

#144533

CREATE TABLE #TAB2 (SNO INT, UNQ_ID BIGINT)
INSERT INTO #TAB2 VALUES(1,10)
i have to increament the value of UNQ_ID by 1 and assign it in one variable....
how it is possible to do it in same time?.. after updating, if i use select statement someother user may update the record... so anybody pls suggest me a better to achieve it?..

Viewing 10 posts - 1 through 9 (of 9 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

If you perform the update within a transaction, other users should not be able to access.

Are you doing this to create an identification generation engine? Or, to put it another way, are you creating a unique identifier creator? If so, you should do some searches in the script section at SSC. There are several ways of doing this, some better than others, but you don't have to invent the wheel yourself.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 2

Hi Grant.

yes. i am doing this for generating unique number. Thanks for ur valuable info.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

MonsterRocks,

This can be done using a feature in SQL Server that will update a variable and a table in the same statement.

CREATE TABLE #TAB2 (SNO INT, UNQ_ID BIGINT)

INSERT INTO #TAB2 VALUES(1,10)

I'm assuming you don't really want to do this in a temp table since there's no reason to do it in a temp table.

DECLARE @SomeBigInt BIGINT

UPDATE #TAB2

SET

@SomeBigInt = UNQ_ID

, UNQ_ID = UNQ_ID + 1

You don't need an explicit transaction since it is done in a single statement, which is already in implicit transaction.

Todd Fifield

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

MonsterRocks (1/9/2011)
CREATE TABLE #TAB2 (SNO INT, UNQ_ID BIGINT) INSERT INTO #TAB2 VALUES(1,10)
i have to increament the value of UNQ_ID by 1 and assign it in one variable....
how it is possible to do it in same time?.. after updating, if i use select statement someother user may update the record... so anybody pls suggest me a better to achieve it?..

Why can't you just add the IDENTITY property to the UNQ_ID column? It will help you avoid a whole lot of headaches including but certainly not limited to the deadlocks that inevitably occur when you try to build your own unique sequences.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

Jeff Moden - 1/10/2011
Why can't you just add the IDENTITY property to the UNQ_ID column? It will help you avoid a whole lot of headaches including but certainly not limited to the deadlocks that inevitably occur when you try to build your own unique sequences.

Jeff,

I think this depends on the type of application. I've never seen any deadlocks using this technique for a Point of Sale system I work with. The cash registers request the next ticket number using this technique. Since there's no way people can check out faster than 1 per minute per cash register, there's no problem

I would never use this technique to get sequential numbers in a rapid fire environment.

Todd Fifield

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

tfifield (1/11/2011)
Jeff Moden - 1/10/2011
Why can't you just add the IDENTITY property to the UNQ_ID column? It will help you avoid a whole lot of headaches including but certainly not limited to the deadlocks that inevitably occur when you try to build your own unique sequences.
Jeff,
I think this depends on the type of application. I've never seen any deadlocks using this technique for a Point of Sale system I work with. The cash registers request the next ticket number using this technique. Since there's no way people can check out faster than 1 per minute per cash register, there's no problem
I would never use this technique to get sequential numbers in a rapid fire environment.
Todd Fifield

It doesn't have to be a rapid fire environment though I agree that it's less likely to happen if it's not.

Still, my question stands... even on a point of sale system, why would you prefer a "manually programmed calculation" over something the server was designed to do on it's own?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 7

Jeff,

The main reason for using a sequence table is so that each store and even each register can have their own sequence of tickets.

The ticket numbers for the stores might look like:

01-10001 (Store 1)

02-20001 (Store 2)

or

01-02-10001 (Store 1 Station 2)

03-01-30001 (Store 3 Station 1)

and so forth.

The sequence table has 1 row per store (if it's done by store) or 1 row per store/register (if it's done by register). Each row has the next ticket number for that store (or register).

There is a function that will increment the numeric part of the ticket number after the hyphen or last hyphen.

It makes for ticket numbers that are easy to eyeball if need be.

Like I said, we've never had any trouble with blocking since even with 10 stores and 20 registers apiece it's really not that much activity in the database world. It's still customers checking out so it's no big deal.

Todd Fifield

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 8

Exactly my requirement is more or less same like what tfifield mentioned.

And Jeff. i cant use AUTO INCREMENT for that column as it is requirement.

So i go with following query

declare ret_value bigint

Update #tab2 set ret_value=UNQ_ID=UNQ_ID+1

Thanks a lot Jeff and tfifield

tfifield SSCrazy Eights Points: 9655 More actions · Answer 9

tfifield

SSCrazy Eights

Points: 9655

February 1, 2011 at 1:24 pm

#1281123

Glad to be of help.

Todd Fifield