Re: Subtracting from a set of rows/columns

Martin T. wrote:
> Greetings,
>
> The following situation:
> TABLE test_counters ...
> SORT_NUMBER | TEST_ID | COUNT1 | COUNT2 | COUNT3 | COUNT4 | COUNT5
>
> I need to subtract from all counts of a test_id the values of the
> respective count columns for sort_number zero.
> (ie: count* = count* - count*_where_sort_number_zero)
>
> I have implemented it now as follows:
> ---
> v_counts test_counter%rowtype;
> ...
> SELECT *
> INTO v_counts
> FROM test_counters
> WHERE SORT_NUMBER=0
> and TEST_ID = :p_new_id;
>
> UPDATE MEASURES
> SET COUNT1 = COUNT1 - v_counts.count1,
> COUNT2 = COUNT2 - v_counts.count2,
> COUNT3 = COUNT3 - v_counts.count3,
> COUNT4 = COUNT4 - v_counts.count4,
> COUNT5 = COUNT5 - v_counts.count5
> WHERE ORDER_ID = p_new_order_id;
> ---
>
> Is there a more elegant way to do it?
>
> cheers,
> Martin
>

Seriously consider a redesign as:

CREATE TABLE test_counters (
sort_number NUMBER,
test_id VARCHAR2(30),
count_id NUMBER(2),
count_value NUMBER);

Think vertically.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org