Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

updating a column Expand / Collapse
Author
Message
Posted Sunday, January 09, 2011 4:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, 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?..
Post #1044966
Posted Sunday, January 09, 2011 9:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 14,840, Visits: 27,315
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1044998
Posted Sunday, January 09, 2011 9:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, Visits: 350
Hi Grant.

yes. i am doing this for generating unique number. Thanks for ur valuable info.
Post #1045000
Posted Monday, January 10, 2011 10:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, January 27, 2014 7:07 PM
Points: 959, Visits: 2,879
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
Post #1045435
Posted Monday, January 10, 2011 7:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1045660
Posted Tuesday, January 11, 2011 2:22 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, January 27, 2014 7:07 PM
Points: 959, Visits: 2,879

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
Post #1046162
Posted Tuesday, January 11, 2011 6:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1046219
Posted Tuesday, January 11, 2011 7:34 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, January 27, 2014 7:07 PM
Points: 959, Visits: 2,879
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
Post #1046230
Posted Tuesday, January 18, 2011 4:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 06, 2014 4:32 AM
Points: 127, 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
Post #1049185
Posted Tuesday, February 01, 2011 1:24 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, January 27, 2014 7:07 PM
Points: 959, Visits: 2,879
Glad to be of help.
Todd Fifield
Post #1057041
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse