SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


updating a column


updating a column

Author
Message
BeginnerBug
BeginnerBug
Old Hand
Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)

Group: General Forum Members
Points: 363 Visits: 350
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?..
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54821 Visits: 32794
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
BeginnerBug
BeginnerBug
Old Hand
Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)

Group: General Forum Members
Points: 363 Visits: 350
Hi Grant.

yes. i am doing this for generating unique number. Thanks for ur valuable info.
tfifield
tfifield
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 2890
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
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114990 Visits: 41409
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tfifield
tfifield
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 2890

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
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114990 Visits: 41409
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tfifield
tfifield
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 2890
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
BeginnerBug
Old Hand
Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)Old Hand (363 reputation)

Group: General Forum Members
Points: 363 Visits: 350
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
tfifield
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1555 Visits: 2890
Glad to be of help.
Todd Fifield
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search