September 1, 2003 at 3:54 am
Dear All,
I have a one-time conversion problem that I would like
your advice on.
I have a table which holds has four columns, ID, RoleID,
OwnerID, and RoleType, all are ints except for RoleType
which is a char(30)
The Data within this table is set as follows
ID RoleID OwnerID RoleType
1 20 32 LIFE
2 20 33 LIFE
3 21 32 LIFE
4 22 42 LIFE
5 22 43 LIFE.
Here is what I want to do. Looking at the RoleID I would
like to change the RoleType to FirstLife for the first
occurrence of the same RoleID, and SecondLife for the
second occurrence, there will only be a maxiumum of two
occurences, so the data will be changed as follows:-
ID RoleID OwnerID RoleType
1 20 32 FirstLife
2 20 33 SecondLife
3 21 32 FirstLife
4 22 42 FirstLife
5 22 43 SecondLife
Can anyone help me with the SQL for this ?
thanks
J
September 1, 2003 at 4:41 am
JulieW,
Heres an idea, it's a bit ugly but should work - though you need 2 queries. Hopefully someone else has a suggestion because I'd like to know a nice way to do it as well.
update @tablename
set roletype = 'SecondLife'
where id in
( select max(id) as id from @tablename
where roleid in
(
select roleid from (select count(1) x , roleid from @tablename group by roleid) z where x > 1
)
group by roleid
)
go
update @tablename
set roletype = 'FirstLife'
where roletype <> 'SecondLife'
go
Michael.
September 1, 2003 at 4:45 am
Thanks Michael,
I will give it a try
J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply