|
|
Posted by Roy Harvey on 06/26/07 20:48
First, SUM acts across multiple rows, but you appear to simply be
adding up three columns. After that and other changes we have
UPDATE customer_tbl
SET customer_rank = ordered2004 + ordered2005 + ordered2006
WHERE ordered2004 + ordered2005 + ordered2006 <> customer_rank
OR customer_rank IS NULL
So it simply assigns the new value IF it is any different than the old
one.
But there may be a better way, which is not to have a customer_rank
column at all. It is easily derived from the other columns as needed.
If you must have it, consider making it a computed column rather than
a physical column. Or it could be added to a view. Either a computed
column or the column in a view can be indexed, see the Books on Line
for restrictions.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 20:33:07 -0000, azriley@gmail.com wrote:
>I have inherited a database that tracks if a customer ordered a
>product, with 1 being a yes and 0 being no. What I want to do is sum
>those columns (customer_tbl.ordered2004, customer_tbl.ordered2005,
>customer_tbl.ordered2006) and set the value of that sum into a column
>in the same table (customer_tbl.customer_rank).
>
>Short of doing a
>
> UPDATE customer_tbl
> SET customer_rank = 3
> WHERE SUM(ordered2004 + ordered2005 + ordered2006) = 3
>
>Is there a better way to update each row's customer_rank based on its
>sum of ordered columns?
>
>Any help would be appreciated.
Navigation:
[Reply to this message]
|