SQL string question

[Ajak]

Hi all,

I have a situation here. I have a table (Options) which has the following
single record {field}={value}:-

Quota=50 (smalllint)
CarryFwd=1 (bit - can be 0 or 1)
CarryFwdMax=20 (smallint)

And I have a table (Users) which has the concerned field called AvailCredit
as one of its fields.

I would like to update the field AvailCredit based on the values in Options
table as follows:-

If Options.CarryFwd value is 0 then
Users.AvailCredit will be changed to Options.Quota regardless of what it's
initial value is.

If Options.CarryFwd value is 1 then
Users.AvailCredit will be changed to Options.Quota+(Users.AvailCredit or
Options.CarryFwdMax, whichever is lower)

How do I write the SQL string as a single execution without having to read
and update each users record in the application's for...next loop? Is that
possible?

I'm using MS SQL for the database.

Thanks!

[Bob Barrows]

Ajak wrote:

> Hi all,
>
> I have a situation here. I have a table (Options) which has the
> following single record {field}={value}:-
>
> Quota=50 (smalllint)
> CarryFwd=1 (bit - can be 0 or 1)
> CarryFwdMax=20 (smallint)
>
> And I have a table (Users) which has the concerned field called
> AvailCredit as one of its fields.
>
> I would like to update the field AvailCredit based on the values in
> Options table as follows:-
>
> If Options.CarryFwd value is 0 then
> Users.AvailCredit will be changed to Options.Quota regardless of what
> it's initial value is.
>
> If Options.CarryFwd value is 1 then
> Users.AvailCredit will be changed to Options.Quota+(Users.AvailCredit
> or Options.CarryFwdMax, whichever is lower)
>

This is off the top of my head so it is not tested.

UPDATE u
SET AvailCredit =
CASE CarryFwd WHEN 0 THEN
Quota
ELSE
CASE WHEN CarryFwdMax > Quota + AvailCredit THEN
Quota + AvailCredit
ELSE
CarryFwdMax
END
END
FROM Users u CROSS JOIN Options o

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.