need help on sql statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

need help on sql statement

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2002-04-04 : 11:35:01

this works
update due_evals
set pl_updated_by = '000212',
pl_updated_date = '4/4/2002',
payloc_comp_date = case due_evals_id
when 179624 then '02/20/2002'
where due_evals_id in (179624)

however i want to add another field to the case portion (rating)
this does not work. However is the another way of doing this
update due_evals
set pl_updated_by = '000212',
pl_updated_date = '4/4/2002',
rating,payloc_comp_date = case due_evals_id
when 179624 then '02/20/2002'
when 179624 then 'competent'
where due_evals_id in (179624)

Jay99

468 Posts

Posted - 2002-04-04 : 11:44:23


update due_evals 
set pl_updated_by    = '000212', 
    pl_updated_date  = '4/4/2002', 
    rating           = 'competent',
    payloc_comp_date = '02/20/2002'
where
    due_evals_id = 179624

<O>
First off, neither of your UPDATE statements should work. You have no END on your CASE in the first UPDATE. Second, restrict the update to due_evals_id = 179624 (your where clause) the case due_evals_id will always evaluate to 179624, thus making your CASE worthless. Finally, UPDATE statemente syntaxt should be . . .


update table_name
set column_name = expression
where
   <search_condition>

so your rating,payloc_comp_date = is incorrect syntax.

Jay
<O>
fixed it Jay

Edited by - robvolk on 04/04/2002 11:50:15

Edited by - Jay99 on 04/04/2002 12:07:38

izaltsman
A custom title

1139 Posts

Posted - 2002-04-04 : 11:47:54

Umm... The first UPDATE ain't gonna work either -- you haven't closed your CASE statement with an END keyword

And the second UPDATE statement has a whole bunch of problems... First of all you didn't close the CASE statement. Secondly, you haven't assigned any value to the RATING field. And finally (assuming PAYLOC_COMP_DATE is a datetime field), you are trying to assign a varchar value ('competent') to a datetime field.

Sniped again!

---------------
Strong SQL Developer wanted in the Boston area. Please e-mail if interested.

Edited by - izaltsman on 04/04/2002 11:49:13

csphard
Posting Yak Master

113 Posts

Posted - 2002-04-04 : 11:51:13

Thanks for your help. The following will work. However is there an
easier way of doing this?

update due_evals
set pl_updated_by = '000212',
pl_updated_date = '4/4/2002',
payloc_comp_date = case due_evals_id
when 179624 then '02/20/2002'
when 194162 then '02/22/2002' end
where due_evals_id in (179624,194162)

update due_evals
set rating = case due_evals_id
when 179624 then 'competent'
when 194162 then 'outstanding' end
where due_evals_id in (179624,194162)

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-04 : 11:51:45

I think you have to split it up like this: (You forgot the END's on the case!! BOL is your friend.)

update due_evals
set pl_updated_by = '000212',
pl_updated_date = '4/4/2002',
rating = case due_evals_id when 179624 then 'competent' END ,
payloc_comp_date = case due_evals_id when 179624 then '02/20/2002' END
where due_evals_id in (179624)

Doh double sniped!
Michael

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-04 : 12:00:19

If you have LOTS of these cases, you probably need to create some tables that contain this stuff.
A ratings table:
ID Rating
179624 competent
194162 outstanding

You also need one to handle the pl_updated_date

Then you statement becomes something like this:

update due_evals
set rating = (SELECT rating FROM tblRatings where Id IN(179624,194162)),
pl_updated_date = (SELECT updated_date FROM tblPaylocCompDate where Id IN(179624,194162))
where due_evals_id in (179624,194162)

Michael

Jay99

468 Posts

Posted - 2002-04-04 : 12:11:02

quote:

Thanks for your help. The following will work. However is there an
easier way of doing this?

update due_evals
set pl_updated_by = '000212',
pl_updated_date = '4/4/2002',
payloc_comp_date = case due_evals_id
when 179624 then '02/20/2002'
when 194162 then '02/22/2002' end
where due_evals_id in (179624,194162)

update due_evals
set rating = case due_evals_id
when 179624 then 'competent'
when 194162 then 'outstanding' end
where due_evals_id in (179624,194162)


update 	due_evals 
set     pl_updated_by = '000212', 
        pl_updated_date = '4/4/2002', 
        payloc_comp_date = case due_evals_id 
                               when 179624 then '02/20/2002' 
                               when 194162 then '02/22/2002'
                           end,
        rating           = case due_evals_id
                               when 179624 then 'competent' 
                               when 194162 then 'outstanding'
                           end
where
    due_evals_id in (179624,194162)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources