SQL Server - Mass update using calculated value

I have a mass-update going on which increases a fixed-decimal column by a
user-specified percent.  The query needs to ignore rows which would cause
the newly calculated ddisc to overflow the column.  Is there a better or
more efficient way to form the update query without repeating the
calculation?  There may be thousands of rows updated.


DECLARE @pct decimal(5,4)
CREATE TABLE #CntrPrice
(  cusno char(5) NOT NULL,
part char(25) NOT NULL,
ddisc decimal(4,3) NOT NULL,
dlst_chng_dt datetime,
PRIMARY KEY (Cusno,Part)
)

UPDATE cntrPrice
SET ddisc = cntrPrice.ddisc * (1 + @pct),dlst_chng_dt = GETDATE()
FROM #CntrPrice cntrPrice
/* JOIN to other tables to limit cusno based on other user criteria */
/* JOIN to other tables to limit part based on other user criteria */
WHERE 9.999 >= cntrPrice.ddisc * (1 + @pct)
/* AND other user criteria */

what about a CASE statement?

--

Jack Vamvas
___________________________________
Search  IT jobs from multiple sources-   http://www.ITjobfeed.com

DECLARE @pct DECIMAL(5,4);

CREATE TABLE CntrPrice
(cust_nbr CHAR(5) NOT NULL,
part_nbr CHAR(25) NOT NULL,
ddisc DECIMAL(4,3) NOT NULL,
dlst_chng_dt DATETIME,
PRIMARY KEY (cust_nbr, part_nbr));

Using Standards SQL, you can use this skeleton and avoid the problems
with the proprietary UPDATE.. FROM.. syntax (Google the errors you can
in your data

UPDATE CntrPrice
SET dlst_chng_dt = CURRENT_TIMESTAMP,
ddisc
= CASE WHEN (ddisc * (1.0000 + @pct)) > 9.999
THEN (ddisc * (1.00 + @pct)
ELSE ddisc END
WHERE cust_nbr IN (<<limit cusno based on other user criteria >>)
AND part_nbr IN (<<limit part_nbr based on other user
criteria>>);

My inclination would be to ignore the cost of computing that value twice.
It is almost certainly effectively 0 compared to the cost of accessing and
updating your data.  If you are having performance problems with this update
it is almost certainly in the cost of retrieving the data.  Actually
updating thousands of rows is not normally a problem for SQL Server.  If you
look at the query plan for this update there will probably be a step called
I bet it says it's 0% of the total cost of that update.

Tom

This query does not do the same thing as the query posted by the OP.  The
OP's query does not change the dlst_chng_dt for rows where the calculated
amount is > 9.999, your query changes dlst_chng_dt in every row.

Tom

e

But at least he is using "Standards SQL"!  :b