Basic Structure - 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 2008 Forums

Transact-SQL (2008)

Basic Structure

Author

Topic

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-14 : 17:57:14

Hey, I have a complicated situation here.
I want to update a table my_table according to the following scenario.
COLA has two values, val1 or val2. forget about val2.
now if colA = val1 then it should look for colB. ColB in turn can have 4 options. (opt1, opt2,....)

when colA = Value1
case = colB
when opt1 then update colC
when opt2 then update colD
end

please write a query.

Many thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-14 : 18:01:58

UPDATE myTable SET colA=CASE colB
WHEN 'opt1' THEN colC
WHEN 'opt2' THEN colD
ELSE colA END
WHERE colA='val1'

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-07-14 : 18:44:24

Please correct this query:

update TestDriverTable
SET Action = CASE Name
WHEN 'Provider' THEN ( set ID1 = NewID
From
(select ID1, NewID = case Name
when 'Provider' then 'PV0'
end

+ right('000' + convert(varchar(15), row_number() over(partition by Name order by Name) + 99 ), 15)
FROM TestDriverTable
)
TestDriverTable
)

ELSE 'nothing' END
WHERE Action='Insert'

Please note that I have two actions "insert or update" Need to update the table where action = insert.
However if I run inside query, It returns me good values but gives me an error when I run as a whole.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-14 : 22:57:08

Here's the Transact-SQL syntax for UPDATE: http://msdn.microsoft.com/en-us/library/ms177523.aspx

Here's another site too: http://www.w3schools.com/sql/sql_update.asp

I can't "correct" your query because it's not clear what you're trying to do. I suggest you try to work it out for yourself first, based on the links I provided and the example I posted. Hint: the SET statement does not go inside a CASE expression. You have a lot of pieces to get right before you can combine them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources