February 23, 2009 at 12:04 am
I want to update a table's column data with serial number i.e. first row 1, 2nd row 2, 3rd row 3 etc.
I now to generate serial number in resultset using ROW_NUMBER() function but how can I update a table with it.
February 23, 2009 at 12:24 am
Another method to do it ...
Add the column you want to store serial numbers as identity column
Alter table table1 Add serialnumner INT IDENTITY(1,1)
"Keep Trying"
February 23, 2009 at 12:28 am
Do you have any columns that have unique values. For using the rownumber function unique columns will be required to make the joins.
a very basic example...
create table #t (col1 varchar)
INSERT INT O #t values ('c')
INSERT INT O #t values ('d')
INSERT INT O #t values ('e')
update #t set col2 = rownum
FROM (select col1,row_number() over (order by col1) as rownum from #t) D
WHERE D.col1 = #t.col1
col1 contains unique values so its possible to make the join on col1.
"Keep Trying"
February 23, 2009 at 12:40 am
Thanks for your response.
I know about the identity field, but I have to update serial number only for selected records and its not a primary key field.
Following query can help you understand the requirement. I'm trying to do it but its not working
UPDATE tab1 SET seqCol = (SELECT ROW_NUMBER() OVER (ORDER BY CreationDate) FROM tab1 where FK_ID=6) where FK_ID=6
February 23, 2009 at 3:41 am
create temp table usign Identity column and then fill the data into this temp table.
Now you have the seq. data into the temp table, now it's time to update the base table based on primary field.
hope this will help you.
February 23, 2009 at 3:49 am
is created date unique for each record in that set of records.
"Keep Trying"
February 23, 2009 at 3:53 am
Isn't there any other way without creating temporary table i.e. using new features in SQL 2005/8 CTE etc.
February 23, 2009 at 4:14 am
You can update a derived table:
UPDATE D
SET seqCol = RowId
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY FK_ID ORDER BY CreationDate) AS RowId
        ,seqCol
    FROM tab1
    WHERE FK_ID = 6
) D
February 23, 2009 at 9:35 am
If using row_number() make sure you have "uniqueness" in your over() part or the results can be non-deterministic!
* Noel
February 23, 2009 at 9:39 am
It sounds to me like what you need is a view with a row_number column, instead of adding a column to the actual table. That way, you can include a Where clause in it, and whatever level of complexity you need for the row_number data.
With that, you should be able to get what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2009 at 11:27 pm
Yes a view is a nice idea but it will work only if the serial numbers dont need to be presisted in the database.
"Keep Trying"
February 25, 2009 at 1:47 am
Thanks for your support. I like your query but there's a problem.
I didn't execute your query yet because I didn't find place to put WHERE clause in UPDATE statement and I'm afraid without WHERE it may update all rows in table.
Actually I want to update only rows fullfilling specific criteria with serial numbers. I hope you understand my problem.
February 25, 2009 at 10:53 pm
Try this in your test environment.
UPDATE D
SET seqCol = RowId
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY FK_ID ORDER BY CreationDate) AS RowId
,seqCol
FROM tab1
WHERE FK_ID = 6
) D
-- where clause
WHERE "your condition"
"Keep Trying"
April 25, 2018 at 10:13 pm
Is this what you are looking for?
select row_number() over (partition by rand() order by rand() ) From <table_name>
April 30, 2018 at 11:10 am
microsoftee - Monday, February 23, 2009 12:04 AMI want to update a table's column data with serial number i.e. first row 1, 2nd row 2, 3rd row 3 etc.I now to generate serial number in resultset using ROW_NUMBER() function but how can I update a table with it.
I don't think you know what a table or serial number is. Do you have any manufactured products sitting around reading see it? Look for the serial number on your phone, radio or whatever. Serial numbers are by definition assigned by an external source and have nothing whatsoever to do with the "physical position" of rows in the table. The serial numbers may include letters and digits, punctuation marks and check digits. Designing a serial number can be tricky and requires careful thought.
It looks like you're trying to sequentially number a table. But that's absurd! Rows have no ordering by definition! So there's no such thing as a first row, second row, etc. This is usually covered the first week of any class on relational databases. In fact, one of the basic principles of RDBMS is that rows are located not by a physical position, but by a logical key. Exactly what are you trying to do with this table?
Part of the serial number can be a sequence (please look up the CREATE SEQUENCE statement ore read https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/).
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply