Posted by Ed Murphy on 06/27/07 04:43

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)

Eww. Those columns should be ditched, in favor of a separate table
with columns 'customer_id', 'year', 'ordered'. The person who saddled
you with those columns should also be ditched.

> 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?

Under the current design:

update customer_tbl
set customer_rank = ordered2004 + ordered2005 + ordered2006

Under the repaired design:

update customer_tbl
set customer_rank = sum(cy.ordered)
from customer_tbl c
join customer_year_tbl cy on c.customer_id = cy.customer_id
group by c.customer_id

[Back to original message]