June 27, 2005 at 11:28 am
I have a table w/ 2 columns:
prop_ID attribute_id
ENT NULL
FLTL NULL
FLTU NULL
GFOR NULL
HCOM NULL
HFOR NULL
LVOL NULL
RI NULL
SOLP NULL
SSTD NULL
I need to assign each prop_ID an attribute_id, which is consequtive integer : 1,2,3,4,5,6...n
All Prop_id in the table are unique.
It does not matter which prop_ID receives what attribute_ID, the only requirement is for all
attribute_id to be unique.
Thanks,
Sergei
June 27, 2005 at 11:34 am
Create table X
(
Name varchar(25) not null
)
GO
Insert into dbo.X (name)
Select 'a'
UNION ALL
Select 'b'
UNION ALL
Select 'd'
UNION ALL
Select 'e'
UNION ALL
Select 'c'
UNION ALL
Select 'g'
GO
Select * from dbo.X
GO
ALTER TABLE X
ADD Ident int identity (1,1)
GO
Select * from dbo.X
DROP TABLE X
June 27, 2005 at 11:44 am
Hi, Remi
from what u wrote I got:
a 1
b 2
d 3
e 4
c 5
g 6
pls explain how it can help me to do what i wanted to be done?
or do u suggest for me to type all those prop_ID by hand? The only problem is that I have 1600 of those unique prop_ID in the table. That was the reason for the post.
Sergei
June 27, 2005 at 11:48 am
just drop the column with all the nulls in it then recode this script :
ALTER TABLE YourTableName
ADD attribute_id int identity (1,1)
June 27, 2005 at 12:00 pm
I cannot drop the column 'attribute_id' b/c it already contains non-NULL entries for the other 'prop_ID'. I guess I'll have to create a temp table with those prop_id for which attribute_id IS null, add an identity column to the temp table and then update 'attribute_ID' in the original table vy INNNER JOIN with the temp.
Thanks, Remi
Sergei
June 27, 2005 at 12:06 pm
That's one way of doing this... What problem r u trying to solve with this??
June 27, 2005 at 12:18 pm
probably did not give enough details...
prop_ID attribute_ID
ACEN 2001
AIT 2003
DM 1194
ENT NULL
FLTL NULL
FLTU NULL
FLVL 2040
FLVU 2087
FP 1200
GFOR NULL
need to assign a unique attribute_id (starting with 105001) where attribute_ID is NULL, ie replace NULLs with consecutive intergers. hope it explains it better
can it be done w/o resorting to temp table?
Sergei
June 27, 2005 at 12:23 pm
What will the new column data mean?
Whyuse a random number in there (seems wrong from a design perspective)?
June 27, 2005 at 12:34 pm
the numbers in 'attribute_id' columns are not random. each property has an unique 'attribute_id' in the database. I just showed u top 10 records from the table. What I'm doing is adding new, not yet used, properties to the relational database. Therefore I need to assign them an unique 'attribute_id' for web applications to be able to deal with them.
I already figured (see perviuos post) that I can update those NULLs with conseq integers using temp table. But I'm just wondering if there is a way to do it directly in the original table? - maybe using self-join?
Thanks
Sergei
June 27, 2005 at 1:15 pm
that's how it can be done with temp table:
--create a temp table
select propertyID, att_id_new into _temp1
from _all_props where att_id_new is null
--drop 'att_id_new' column
ALTER TABLE _temp1 DROP COLUMN att_id_new
-- add identity column starting with 105001
ALTER TABLE _temp1
ADD att_id_new int identity (105001,1)
--update 'att_id_new' in orig table
update _all_props
set att_id_new = Tmp.att_id_new from
_all_props AP INNER JOIN _temp1 Tmp
on AP.propertyID = Tmp.propertyID
Does anyone know a faster way (w/o using temp table)?
Thanx
Sergei
June 27, 2005 at 1:20 pm
A self join as you said will do it :
Create table X
(
Name varchar(25) not null,
Ident int null
)
GO
Insert into dbo.X (name, ident)
Select 'a', 3
UNION ALL
Select 'b', null
UNION ALL
Select 'd', null
UNION ALL
Select 'e', 15009
UNION ALL
Select 'c', 563
UNION ALL
Select 'g', null
GO
Select * from dbo.X
UPDATE XMain set Ident = dtIdent.NewIdent from X XMain inner join
(Select X1.Name, count(*) + 105000 as NewIdent from dbo.X X1 inner join dbo.X X2 on X2.name <= X1.Name and X1.ident is null and X2.ident is null group by X1.name)
dtIdent ON XMain.Name = dtIdent.Name
Select * from dbo.X
GO
DROP TABLE X
This assumes that the Name columns is unique.
June 27, 2005 at 1:33 pm
It works just fine. It's a self join after all.
Thanks a lot, Remi.
Sergei
June 27, 2005 at 1:43 pm
HTH.
June 28, 2005 at 3:41 am
What about:
declare @v1 int
set @v1 = 105002
update a
set a.attribute_id = @v1 = @v1+1
from YourTable a
where isnull(a.Prop_ID,0) = 0
June 28, 2005 at 4:52 am
Sorry !!
where isnull(a.attribute_id,0) = 0
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply