simple sql question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

simple sql question

Hi. Please help me. Table ‘Counter’ has a date field and an integer field. I want to auto increase the integer field by 1 using the update statement. I DO NOT want to first run a select statement to find out the initial value of the integer field prior to incrementing it. how would i go about doing this? Thanks a lot!
Refer to SQL Server 2005 books online about IDENTITY column in thise case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Not sure if this is what you’re looking for:
CREATE TABLE SEQ_T
(
col1 INT DEFAULT 0
)
DECLARE @SEQUENCE INT
SET @SEQUENCE = 0
WHILE @SEQUENCE <10
BEGIN
INSERT INTO SEQ_T DEFAULT VALUES
SET @SEQUENCE = @SEQUENCE+1
END
SET @SEQUENCE = 0
UPDATE SEQ_T SET @SEQUENCE = col1 = @SEQUENCE + 1
SELECT * FROM SEQ_T
DROP TABLE SEQ_T col1
———–
1
2
3
4
5
6
7
8
9
10 (10 row(s) affected) The only drawback here is, that you have no control over the order in which the rows are updated. It might or might not match your expectations. And if that doesn’t help you, please explain with an example what you are looking for.

Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
]]>