October 7, 2015 at 3:02 pm
If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...
I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
October 7, 2015 at 3:16 pm
Some ddl and sample data would help here but this should demonstrate one way of doing this.
create table #HH835HP
(
SomeValue uniqueidentifier
, ID_1 int
, CHKDTS int
);
insert #HH835HP
select top 10 NEWID(), ROW_NUMBER() over (order by newid()), ROW_NUMBER() over (order by newid())
from sys.all_columns; --Just generates 10 rows of randome data
select * from #HH835HP; --This will show you the existing data
with cte as
(
select SomeValue, ID_1, ROW_NUMBER() over (order by CHKDTS) as RowNum
from #HH835HP
)
update cte
set ID_1 = RowNum;
select * from #HH835HP; --Now ID_1 is updated
drop table #HH835HP;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2015 at 3:17 pm
No need
Found answer....
DECLARE @id INT
SET @id = 0
UPDATE accounts2
SET @id = id = @id + 1
GO
October 8, 2015 at 8:39 am
In SQL SERVER 2012 and above.
you can use a sequence like this
CREATE SEQUENCE [dbo].[SEQ1]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE
GO
ALTER TABLE tableName ADD ColumnName INT CONSTRAINT SEQ1 DEFAULT NEXT VALUE FOR [dbo].[SEQ1]
October 8, 2015 at 9:14 am
mw112009 (10/7/2015)
No needFound answer....
DECLARE @id INT
SET @id = 0
UPDATE accounts2
SET @id = id = @id + 1
GO
This technique is what many people refer to as a "Quirky update" which is undocumented. If you are going to use this method you should take a look at this article[/url] by Jeff Moden. There are some "rules" that you should follow if you are going to do this which are discussed in the article.
Note that, if you don't need the numbers in any particular order you could refactor Sean's solution like this:
with cte as
(
select SomeValue, ID_1, ROW_NUMBER() over (order by (SELECT NULL)) as RowNum
from #HH835HP
)
update cte
set ID_1 = RowNum;
Another way to do this is to create a new table and dump there values there using identity like this:
-- using sean's sample data
CREATE TABLE #newtable (SomeValue varchar(100), id int identity)
INSERT #newtable (SomeValue)
SELECT SomeValue
FROM #HH835HP
or like this:
SELECT identity(INT,1,1) AS id, SomeValue
INTO #newtable2
FROM #HH835HP
Both of these techniques will perform as well as the quirky update and work as shown above without any other changes required.
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply