Help on using CASE together with UPDATE

Hi
I need some help on how to update some fields with a value based on the
value in another field. I have tried to do this with a CASE statement, but I
haven't really been able to get anywhere near something that works...
If I run the select statement -
"select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
a.zipcode, z.cityname, a.title from user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' ",
then it gives the records I want to update. What I then want to do, is to
update the field u.userdepartment with a value based on the value of the
field "title". Eg. when the field title has the value "IT" then I'd like to
set userdepartment = 2959028.
I'd think that I can use something like ...userdepartment = CASE title = 'IT' then 2959028... but apparently I need some guidedance on how to do
this.
Can any of you help with this?
Regards
Steenuntested code follows:
Does this select statement return what you are looking for?
SELECT user, title,
userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
If so, this might be the update statement that you are looking for:
UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
WHEN 'MARKETING' THEN 1
WHEN '...' THEN 2
ELSE NULL END
FROM user u
JOIN address a on u.addressidentold = a.addressident
JOIN Zipcode z ON a.zipcode=z.zipcode
WHERE u.userinit='spe'
--
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Try Something on these lines:
UPDATE user
SET userdepartment = CASE title
WHEN 'IT' THEN 2959028
WHEN 'HR' THEN 2959029
ELSE NULL
END
From address a
INNER JOIN Zipcode z ON a.zipcode=z.zipcode
where u.userinit='spe' and u.addressidentold = a.addressident
-- Note: code not tested ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need some help on how to update some fields with a value based on the
> value in another field. I have tried to do this with a CASE statement, but
I
> haven't really been able to get anywhere near something that works...
> If I run the select statement -
> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> a.zipcode, z.cityname, a.title from user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> where u.userinit='spe' ",
> then it gives the records I want to update. What I then want to do, is to
> update the field u.userdepartment with a value based on the value of the
> field "title". Eg. when the field title has the value "IT" then I'd like
to
> set userdepartment = 2959028.
> I'd think that I can use something like ...userdepartment = CASE title => 'IT' then 2959028... but apparently I need some guidedance on how to do
> this.
> Can any of you help with this?
> Regards
> Steen
>|||Hi
Thanks to both of you - by "combining" your examples I got it working.
Keith - the second line of your example should be
...userdepartment = CASE title When 'IT' then 29... then it works...
It's always a joy to use this newsgroup - no matter what stupid and simple
question being asked, there're always a lot of helpfull answers to us less
"sql-skilled" people......
Thanks
Steen
.
Keith Kratochvil wrote:
> untested code follows:
> Does this select statement return what you are looking for?
> SELECT user, title,
> userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
> If so, this might be the update statement that you are looking for:
> UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> WHEN 'MARKETING' THEN 1
> WHEN '...' THEN 2
> ELSE NULL END
> FROM user u
> JOIN address a on u.addressidentold = a.addressident
> JOIN Zipcode z ON a.zipcode=z.zipcode
> WHERE u.userinit='spe'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> I need some help on how to update some fields with a value based on
>> the value in another field. I have tried to do this with a CASE
>> statement, but I haven't really been able to get anywhere near
>> something that works...
>> If I run the select statement -
>> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
>> a.zipcode, z.cityname, a.title from user u
>> JOIN address a on u.addressidentold = a.addressident
>> JOIN Zipcode z ON a.zipcode=z.zipcode
>> where u.userinit='spe' ",
>> then it gives the records I want to update. What I then want to do,
>> is to update the field u.userdepartment with a value based on the
>> value of the field "title". Eg. when the field title has the value
>> "IT" then I'd like to set userdepartment = 2959028.
>> I'd think that I can use something like ...userdepartment = CASE
>> title = 'IT' then 2959028... but apparently I need some guidedance
>> on how to do this.
>> Can any of you help with this?
>> Regards
>> Steen|||There are two ways to do CASE. This is the other method. My revised
example should work correctly:
CASE WHEN title = 'IT' THEN 2959028
WHEN title = 'MARKETING' THEN 1
WHEN title = '...' THEN 2
ELSE NULL END
Keith
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Of9LDvGrEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks to both of you - by "combining" your examples I got it working.
> Keith - the second line of your example should be
> ...userdepartment = CASE title When 'IT' then 29... then it works...
> It's always a joy to use this newsgroup - no matter what stupid and simple
> question being asked, there're always a lot of helpfull answers to us less
> "sql-skilled" people......
> Thanks
> Steen
> .
> Keith Kratochvil wrote:
> > untested code follows:
> >
> > Does this select statement return what you are looking for?
> >
> > SELECT user, title,
> > userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> > If so, this might be the update statement that you are looking for:
> > UPDATE user SET userdepartment = CASE WHEN title = 'IT' THEN 2959028
> > WHEN 'MARKETING' THEN 1
> > WHEN '...' THEN 2
> > ELSE NULL END
> > FROM user u
> > JOIN address a on u.addressidentold = a.addressident
> > JOIN Zipcode z ON a.zipcode=z.zipcode
> > WHERE u.userinit='spe'
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:u6Rq8JGrEHA.1296@.TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I need some help on how to update some fields with a value based on
> >> the value in another field. I have tried to do this with a CASE
> >> statement, but I haven't really been able to get anywhere near
> >> something that works...
> >>
> >> If I run the select statement -
> >>
> >> "select u.userinit, u.username, u.userdepartment, u.usergeoplacement,
> >> a.zipcode, z.cityname, a.title from user u
> >> JOIN address a on u.addressidentold = a.addressident
> >> JOIN Zipcode z ON a.zipcode=z.zipcode
> >> where u.userinit='spe' ",
> >>
> >> then it gives the records I want to update. What I then want to do,
> >> is to update the field u.userdepartment with a value based on the
> >> value of the field "title". Eg. when the field title has the value
> >> "IT" then I'd like to set userdepartment = 2959028.
> >> I'd think that I can use something like ...userdepartment = CASE
> >> title = 'IT' then 2959028... but apparently I need some guidedance
> >> on how to do this.
> >>
> >> Can any of you help with this?
> >>
> >> Regards
> >> Steen
>