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
IDRoleIDOwnerID RoleType
12032LIFE
22033LIFE
32132LIFE
42242LIFE
52243LIFE.
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:-
IDRoleIDOwnerID RoleType
12032FirstLife
22033SecondLife
32132FirstLife
42242FirstLife
52243SecondLife
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