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
Steen
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'
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/techinf...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:[vbcol=seagreen]
> 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...
|||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:
>