October 23, 2005 at 1:06 pm
Hi guys,
I am trying to update a table based on certain conditions. I know i can do it by running couple of UPDATE statements but I would like to do it in a better way incase if there is a way. here is the problem description(a simplified example): i have a table with following 3 coloumn names(StudentID, Choice and Status) and values:
StudentID Choice Status
1111 Music S
1111 BackStage P
1111 Dance S
2222 BackStage S
Now my questions is: I would like to change the value of coloumn 'Choice' for the 'StudentID' 1111 if his 'Status' is S to values contained in two variable Choice1(Singing) and Choice2(Assistant). I can just give you a sample of the Output that I require after running the UPDATE query.
StudentID Choice Status
1111 Singing S
1111 BackStage P
1111 Assistant S
2222 BackStage S
I know I can achieve this by two SQL update query but I would like to achieve this in one UPDATE query. Remember, I would like to UPDATE the Choice coloumn in shot itself and not by writing the UPDATE query using 'case when' . The primary key of the table is a composite primary key(StudentID+Choice)
Let me know if anything is not clear.
October 23, 2005 at 1:29 pm
Please post the update statements.
Without using a case, this is the only alternative that comes to mind:
Create table #C
(StudentID, OldChoice , NewChoice)
insert into #c
(StudentID, OldChoice , NewChoice)
select 1111 , 'Music', 'Singing' union all
select 1111 , 'Dance', 'Assistant'
Update Foo
Set Choice = #c.NewChoice
FROM #c
where Foo.StudentID = #c.StudentID
and Foo.OldChoice = #c.OldChoice
drop table #c
SQL = Scarcely Qualifies as a Language
October 23, 2005 at 1:38 pm
The two update statements are:
UPDATE studTable
SET choice=@Choice1
where StudentID=@studID and Choice="Dance" and Status="S"
UPDATE studTable
SET choice=@Choice2
where StudentID=@studID and Choice="Music" and Status="S"
So basically I would like to achieve the above in just one update statement.
October 23, 2005 at 4:35 pm
And why don't you want to use the CASE in this case since this is a conditional update ?
UPDATE studTable
SET choice= (case choice
when 'Dance' then @Choice1
when 'Music' then @Choice2
end)
where StudentID=@studID
and Choice IN ('Music', 'Dance')
and Status='S'
October 23, 2005 at 4:49 pm
The things is that what if both conditions are true. will it perform both the cases? I am not sure about the working of case statements in SQL.
October 23, 2005 at 5:07 pm
The CASE statement is a short circuit statement in T-SQL meaning if the first WHEN condition gets satisfied, the second one does not get executed at all. Since this CASE statement is going to be applied for a given row, for a given row, you will not have both "Music" and "Dance" in the choice column - you will only have one.
So, in other words, if you have say 10 records that qualify the where clause of the update statement and 3 need to be converted for Music, and 7 need to be converted for Dance, that update statement will do it - you can try it out.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply