Re: sum columns into column in same table
- From: Roy Harvey <roy_harvey@xxxxxxxx>
- Date: Tue, 26 Jun 2007 16:48:00 -0400
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@xxxxxxxxx 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.
- References:
- sum columns into column in same table
- From: azriley
- sum columns into column in same table
- Prev by Date: Concat tables into one row in view
- Next by Date: Free client
- Previous by thread: sum columns into column in same table
- Next by thread: Re: sum columns into column in same table
- Index(es):
Relevant Pages
|
