Using case - 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 2005 Forums

Transact-SQL (2005)

Using case

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-29 : 08:50:11

Have following that gives me syntax error:

update #PetroEx_Type_A_Record
set
     #PetroEx_Type_A_Record.splc_code = 
    case
      when select splc_code from FUELFACS.dbo.petroex where host_interface_code = 'PETROEX' then splc_code
    else 
      #PetroEx_Type_A_Record.splc_code = ' '
    end

Incorrect syntax near the keyword 'select'.
Incorrect syntax near the keyword 'then'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-29 : 08:58:06

Can you describe in words what you are trying to do? Also, is there some link between #PetroEx_Type_A_Record and Petroex?

Jim

Everyday I learn something that somebody else already knew

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-29 : 09:01:32

This is what I currently have and it is working, trying to use case instead

---- update SPLC code in Temp Table
update #PetroEx_Type_A_Record
set #PetroEx_Type_A_Record.splc_code = (select splc_code from FUELFACS.dbo.petroex where host_interface_code = 'PETROEX') 
where #PetroEx_Type_A_Record.splc_code = ' '

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-29 : 09:13:42

Ok, Good Luck!

Jim

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 09:20:31

update #PetroEx_Type_A_Record
set splc_code =
case
when a.splc_code = '' then b.splc_code
else a.splc_code
end
from #PetroEx_Type_A_Record a
cross join (select splc_code from FUELFACS.dbo.petroex where host_interface_code = 'PETROEX') b

but your other version is a lot better as it doesn't update rows that remain unchanged.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-29 : 09:49:35

Wouldn't this be better to just hard-code since
select splc_code from FUELFACS.dbo.petroex where host_interface_code = 'PETROEX'
has to return only one value and there is no link between the tables?

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources