Conditions In Update Query?
-
I'm writing an update query which is supposed to set a specific field to a specific value if one set of conditions is met, and to another specific value if another set of conditions is met, and then a third value as a catch-all if none of the criteria is met.
I wrote this in Access using nested iif statements, and it worked; but the same ones don't seem to work the way I want them to in SQL. I can't figure out how to get it done in SQL. Do I have to write a separate block of code for each condition?
-
Try using the SELECT CASE statements.....
-
Check this out.
USE PUBS
UPDATE TITLES SET Type =
CASE type
WHEN 'popular_comp' THEN 'Popular Comp'
WHEN 'mod_cook' THEN 'Mod Cook'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Trad Cook'
END
FROM titles
WHERE price IS NOT NULL
-
SQL keeps rejecting the expressions in the WHEN clause. If I put a value in WHEN, it works; but I need it to be a logical expression.
Any other ides?
-
SHould work fine, can you post what you tried so we can point out maybe a reason why it failed.
-
Here is a much-abridged version of the code:
update TEMPTESTDATA
set Accession# =
Case Accession#
when Substring([TEMPTESTDATA].[LAB_Accession_Num],2,2) = '02'
or Substring([TEMPTESTDATA].[LAB_Accession_Num],2,2) = '01'
or Substring([TEMPTESTDATA].[LAB_Accession_Num],2,2) = '00'
then Right(RTrim([TEMPTESTDATA].[LAB_Accession_Num]),2) + '2120'
when Substring([TEMPTESTDATA].[LAB_Accession_Num],2,2) <> '02'
and Substring([TEMPTESTDATA].[LAB_Accession_Num],2,2) <> '01'
and Substring([TEMPTESTDATA].[LAB_Accession_Num],2,2) <> '00'
then Right(RTrim([TEMPTESTDATA].[LAB_Accession_Num]),2) + '2119'
end
from TEMPTESTDATA
where (((TEMPTESTDATA.LAB_Accession_Num) Like 'N%' Or (TEMPTESTDATA.LAB_Accession_Num) Like 'S%'))
-
Try this
update TEMPTESTDATA
set [Accession#] =
Right(RTrim(LAB_Accession_Num),2) + -- This will happen either way so do here to save some typing and add a little readability.
(
Case-- Didn't understand why you had Accession# here, this is if a single item will compare to mutiple possibilities.
when Substring(LAB_Accession_Num,2,2) IN ('02', '01', '00') -- a group of ORs can be combine into a single IN making easy to read and less to type.
then '2120' -- This is your outcome for condition met.
else '2119' -- Since anything else breaks condition for you then no reason to check, just do else.
end
) -- This outcome will be concatinate to the other piece.
where
LEFT(LAB_Accession_Num,1) IN ('N','S')-- Generally it works faster to compare whole pieces instead of fragments, plus this way you can take advantage of the IN option
Notes:
The reason I removed the TEMPTESTDATA. from everything is because it is the only table referenced and thus it is understood.
I put [] around Accession# becuse of the # in the column name, really just to fully qualify, have had issues with # in columns and this is a safety I prefer.
The from TEMPTESTDATA reference was removed as we are talking about the current table in the UPDATE clause and the reference was wrong.
Also, although not noted here, it has been stated that fully qualifing a table dbname.dbo.tablename or at least dbo.tablename does some some bennifits in processing as the Query engine doesn't need to think about that at all (I have not seen this but MS guys throw this at me often, I say proof is in the pudding but apparrently they don't like pudding.
) -
okay, i figured out another way to do it. apparently you can't use an OR in a WHEN clause, or if you can, the syntax is different
)Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply