update my count

Question

Post reply

update my count

terrence_daniels

SSC Enthusiast

Points: 180
More actions
April 1, 2008 at 8:02 am

#379525

I have a column in my SQL database created for the sole purpose of being able to identify rows. I have numbered the entries in this column in numeric order. Can someone provide me and example of update count script targeting the identified column. 😀

Viewing 15 posts - 1 through 15 (of 17 total)

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

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

Hi,

You can use Identity command. Example shown below.

CREATE TABLE tmp(empno Identity(1,1))

In the above example the column empno is used as identity and hence whenever a data is entered into the table empno column will be automatically incremented one more value and it goes on..

Regards..Vidhya Sagar
SQL-Articles

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 2

the script does not count down the column and insert the correct numerical number. Let me explain in detail

I have nine columns and 1164 rows.

One of the columns is just for number each row. I want to just update that column as needed. So I need the script to start from the first row and count down to the last row then put the results in the afore mention column. 😀

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

note that you need to ORDER BY this identity column to get rows back in numerical order. By default SQL Server does not guarantee row ordering, regardless of what order you insert them in.

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 4

I must be missing something in your explanation. But I tried Insert, Update, and now Order By as you recommended but I get nothing but syntax errors when I run the script. 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Are you renumbering the rows after every insert, or are you looking for a means to update the row_number field as records are inserted? Can more than one row be inserted at a time?

😎

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 6

I am looking for a way to update the row count through a script 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Okay, I guess we are missing the point, so show us. Please provide the DDL for the table, sample data (in the form of an insert statement with union all select statements), and what the expected results of the query you are saking for would be based on the given sample data.

We don't need all the data, just a small sample.

😎

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 8

I will try to oblige:

The column is called Unique_Identifier. the data is just numbers 1,2,3,4, etc...My problem is if I delete any row then my numbers in that column gets off count. I just need a script that will go through that row and count down numerically and then place the results in that column.

The column is not blank it already has data. The data type is int 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Are the row numbers the only way to identify the rows? Does the table have any other means to identify the order the data was entered?

SAMPLE DATA, TABLE DDL, EXPECTED RESULTS; still need this to actually help you.

😎

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 10

Yes it is the only way to identify the rows...this is to avoid any dups...

Sample data provided each entry represents a column I just want to change the data in the last column

Aurora Winfits Mortgages 5.5.5101 No Phone 2129060050 AMRS FICC Winfts Mortgages 6.2 is the latest. 48

Aurora Winfits Options 4.9.4001 No Phone 2129060050 AMRS FICC Winfits Options 6.2 is the latest 49

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

How are new rows added to the table, and when do they get numbered?

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 12

The rows get added manually the numbers get added manually...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Use this as a basis for writing your update query:

create table #MyTable(

RowId int,

RowDataCol1 char(10)

)

go

insert into #MyTable (RowId, RowDataCol1)

select 1, 'Data 1' union

select 2, 'Data 2' union

select 3, 'Data 3' union

select 5, 'Data 5' union

select 6, 'Data 6'

go

select * from #MyTable

go

with NewNumbers (

NewRowId,

OldRowId

) as (

select

row_number() over(order by RowId) as RowNumber,

RowId

from

#MyTable

)

update #MyTable set

RowId = NewRowId

from

NewNumbers nn

inner join #MyTable mt

on (mt.RowId = nn.OldRowId)

go

select * from #MyTable

go

drop table #MyTable

go

terrence_daniels SSC Enthusiast Points: 180 More actions · Answer 14

How come I can not use update instead of create. I do not wish to create a new table, I just want to update the column?