Reply to Re: sum columns into column in same table

Your name:

Reply:


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.

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация