I would like to know whether the following can be done..
DECLARE @IsDefault bit
SELECT @IsDefault = IsDefault FROM <TABLE> WHERE <CONDITIONS>
UPDATE <TABLE>
SET IsDefault = max(IsDefault,@IsDefault) WHERE <CONDITIONS>
Asked
Active
Viewed 655 times
0
marc_s
- 732,580
- 175
- 1,330
- 1,459
user1683982
- 27
- 3
-
Look fro `COALESCE` or `ISNULL`. – MatBailie Mar 17 '14 at 15:12
-
You do know that `variables` can only store 1 value. `@IsDefault` will only have last value assigned. It is not clear what is it that you actually want to do here. – Mar 17 '14 at 15:13
-
Yes...however,is it possible to get the max between the value stored in variable @IsDefault and the value stored in the column IsDefault for the conditions given. – user1683982 Mar 17 '14 at 15:16
-
The answer is probably yes, but your question is too vague. are you trying to set the value of all IsDefault in the table to the max value of IsDefault? – Eccountable Mar 17 '14 at 15:16
-
The idea is to compare the values of @IsDefault (variable) and the value already in the column IsDefault for certain conditions in the WHERE clause,and update with the max of the two. – user1683982 Mar 17 '14 at 15:22
2 Answers
1
The MAX()-function is an aggregate function, and as such you can not use it to compare two values and return only the largest. You have to do something like this instead:
UPDATE <TABLE>
SET IsDefault = CASE WHEN IsDefault > @IsDefault THEN IsDefault ELSE @IsDefault END
WHERE <CONDITIONS>
Similarly for the MIN()-function.
Dan
- 10,480
- 23
- 49
0
On a side note, if you were pulling this value out into a variable to make things more clear, and could in fact use two sets, this is an interesting way to solve this problem that I've needed before.
SELECT [Other Fields],
(SELECT Max(v)
FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]
It should be noted that this only works on sql server >= 2008.
