SQL question: difference between values in tuples

I am posting this for someone who doesn't have net access. Please feel free to post the answer or to respond via email to either of us.

>From: IN%"steele_at_calshp.cals.wisc.edu"
>To: mic_at_macc.wisc.edu
>Subject: SQL question
>
>Hi,
>
>I have a question about databases (Oracle for Macintosh) and
>composing a SQL statement. The table I'm working with has
>the following columns:
>
> Year Firm_Code Capacity
>
>The task is to calculate a Capacity_Change column as capacity in
>year t minus capacity in year t-1 for each Year and Firm_Code.
>
>I modified the original table by adding the column
>Capacity_Change. Next, I tried to update the null values in this
>column using the update command but I keep running into
>problems.
>
>How do I update th Capacity_Change column?
>
>Thanks for your help.
>
>TS

I told him to try the following:

     UPDATE firms F1
     SET cap_chg = (SELECT F1.capacity-F2.capacity 
                    FROM firms F2
                    WHERE F2.firm_code=F1.firm_code
                      AND F2.year=F1.year-1);

He said this returns an error saying:

     Single-row subquery returns no rows

We then tried:

     UPDATE firms F1
     SET cap_chg = (SELECT F1.capacity-F2.capacity 
                    FROM firms F2
                    WHERE F2.firm_code=F1.firm_code 
                      AND F2.year=F1.year-1)
     WHERE F1.year>1950;

Other suggestions?

John G. Heim                                   Internet: heim_at_macc.wisc.edu
UW - Madison Academic Computing Center         Phone:    608-262-9887
1210 W. Dayton St.                             Fax:      608-262-4679
Madison, WI., 53706                            CIS:      76376,3635