Update column as an identity

Question

Update column as an identity

pedro.ribeiro

Hall of Fame

Points: 3201
More actions
March 23, 2009 at 10:49 am

#71402

Hi -
I have a table. This table (table1) as 3 columns (name,address and number).
The fields 1 and 2 (name and address) are already with data.
I need to update the 3 field (number) with incrementing values.
This values have to be bigger then the max value that i have on other table (table2).
Example:
Table2 data:
Petter; street 1; 1
Paul; street 2; 2
Iven; Street 10; 3
......................
.....................
....................
Marta; street 20, 40
In this case i need to populate the field (number) of table1 with values starting from 41 and incrementing in one , until the last row of the table.
Example:
Table1 data (after the update)
Jonash; street 1 ; 41
Sarah; street 2; 42
helena; street 3 ; 43
..........................
I can not make the column (number) of table1 a identity filed, how can i update table1 field to achieve this method?
tks,
Pedro

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's how i would do it.

since the natural "order" of the table is important to you, you cannot use row_number() to populate the number column, so you need to create teh proper table, populate it, and update the broken table.

create table #table2(name VARCHAR(30),address VARCHAR(30),number INT)

insert into #table2(name,address)

select 'Petter','street 1' UNION ALL

select 'Paul',' street 2' UNION ALL

select 'Iven',' Street 10' UNION ALL

select '.......','..............' UNION ALL

select '.......','.............' UNION ALL

select '........','...........' UNION ALL

select 'Marta',' street 20'

CREATE TABLE #TheProperTable (number int identity(1,1) not null primary key,name VARCHAR(30),address VARCHAR(30))

insert into #TheProperTable(name,address)

SELECT name,address FROM #table2

UPDATE #table2

SET #table2.number = #TheProperTable.number

from #TheProperTable

where #table2.name = #TheProperTable.name

and #table2.address = #TheProperTable.address

select * from #table2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

pedro.ribeiro (3/23/2009)
Hi -
I have a table. This table (table1) as 3 columns (name,address and number).
The fields 1 and 2 (name and address) are already with data.
I need to update the 3 field (number) with incrementing values.
This values have to be bigger then the max value that i have on other table (table2).
Example:
Table2 data:
Petter; street 1; 1
Paul; street 2; 2
Iven; Street 10; 3
......................
.....................
....................
Marta; street 20, 40
In this case i need to populate the field (number) of table1 with values starting from 41 and incrementing in one , until the last row of the table.
Example:
Table1 data (after the update)
Jonash; street 1 ; 41
Sarah; street 2; 42
helena; street 3 ; 43
..........................
I can not make the column (number) of table1 a identity filed, how can i update table1 field to achieve this method?
tks,
Pedro

Do any of these tables have a primary key or clustered index?

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pedro.ribeiro Hall of Fame Points: 3201 More actions · Answer 3

No, order is not important.

I just need something, that i send a value and then , this something (maybe a sp) populates some fileds (not all the column) of a table with values , starting from that value that i send to the SP +1

P.s - I can put a index on this table, no problem.

P.s 2- Isn't the function "row_number()" only available in SQL Server 2005 and 2008? In SQL server 2000 this function does not exist , correct?

tks,

Pedro

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Correct... ROW_NUMBER() does not exist in 2k.

Lowell's code looks good to accomplish what you want, I believe. The question would be, what's in the table with the number when you start? That's why I asked what the PK is and whether or not you have a unique clustered index or not... if it's an UPDATE, there must be a unique column in both tables other than the number column. If the target table starts out empty, then Lowell's code will do just dandy.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)