Need help on identifiers! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help on identifiers!

Author

Topic

NgKH
Starting Member

15 Posts

Posted - 2003-04-21 : 13:20:06

I have a table which holds dubplicates of household ids, call this table household, for example,

household_id
1
1
2
3
3
4
4
4
5

I wanted to uniquely identify this data into each row have a unique value in the format below where the duplicate has a number assign to it on the right

re_household_id
1-1
1-2
2-1
3-1
3-2
4-1
4-2
4-3
5-1

how can this be done in SQL?

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-21 : 13:55:54

What else is on the row? And how would you know what row the incremental value should be applied to.

Why do you need this?

Brett

8-)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 10:32:41

Rob's Genius

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19066

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-22 : 10:43:36

Truly in the presence of greatness.

How did you ever come up with that syntax? I don't think BOL will have it.

quote:


DECLARE @col1 varchar(1), @seq int 
SELECT @seq=0 
UPDATE #temp 
SET @seq = SEQUENCE_ID = CASE @col1 WHEN COLUMN_1 THEN @seq+1 ELSE 1 END, 
@col1 = COLUMN_1

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 13:20:03

Yes it does - just can't seem to find it at the moment.
(assume you are talking about the set).

under update

SET @variable = column = expression

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/22/2003 13:24:59

Edited by - nr on 04/22/2003 13:26:55

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-22 : 21:05:20

If it's not in Books Online, I must've gotten it from Inside SQL Server.

And graz had an article on this before I even visited SQL Team:

http://www.sqlteam.com/item.asp?ItemID=765

So thanks for the praise

but it's really not my invention!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 21:14:31

Think it's a bit odd that the statements are executed in the order that they are coded in the update statement (as well as for each row in turn).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 21:24:12

cheers graz

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-22 : 22:02:54

quote:
Think it's a bit odd that the statements are executed in the order that they are coded in the update statement (as well as for each row in turn).

It's also affected by a clustered index on the table, which is a good thing to have if you need to ensure the rows get numbered a certain way. I haven't seen any anomalies lately, but I did have one or two of these operations screw up the numbering. It might've been in 6.5 though.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 22:05:53

Interesting

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-22 : 22:17:50

It's the same as

select @s = @s + name from sysobjects

The result will depend on the order in which the rows are retrieved.
Only with this it is more crucial as if the household_id's are processed out of order then you will get entries for the same id with the same sequence number.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources