Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!usc!samsung!uunet!pds3!ravi
From: ravi@pds3 (Gorur R. Ravi)
Newsgroups: comp.databases
Subject: Re: How to change each row in a table using SQL ?
Message-ID: <1990Nov1.144502.18208@pds3>
Date: 1 Nov 90 14:45:02 GMT
References: <1990Oct25.081639.14155@abblund.se>
Reply-To: ravi@pds3.UUCP (Gorur R. Ravi)
Organization: Project Design Systems, Inc.
Lines: 48

In article <1990Oct25.081639.14155@abblund.se> erik@abblund.se (Erik Sparre) writes:
>I have a problem.
>
>The user entered 900 rows into table. Instead of entering the elapsed time
>in hours and minutes he entered the wall clock time of completion.
>(The system registers flying times for aeroplanes)
>I have the start wall clock time, so it seems possible to recalculate the
>erroneous field with a simple expression like:
>
>for each row do
> elapsed_time := elapsed_time - start_time
>
>
>I can see two possibilities. Either doing the change with an SQL-statement,
>or writing a program to do it.
>
>I haven't much experience with SQL, so maybe you could give me some hints
>on how to solve it in SQL or with a program using SQL facilities.
>
>Oh, BTW, I'm using ORACLE.
>

	I would write a  small Pro*C program for problems like these. There
might be an easier way to do this which I can't think of at this moment.
You need to select ROWID in your SQL statement and get
records using FETCH statement. Then update records in a while loop using
ROWID in the WHERE clause. A partial code might look like this.

	EXEC SQL DECLARE C1 CURSOR FOR SELECT ROWID FROM <TAB>;
	EXEC SQL OPEN C1;
	EXEC SQL WHENEVER NOT FOUND GOTO FINISH;
	while(1)
	{
		EXEC SQL FETCH C1 INTO :rowid;
		rowid.arr[rowid.len] = '\0';
		
		EXEC SQL UPDATE <TAB> SET ERR_FLD = <expr> WHERE
		ROWID = :rowid;
	}
	FINISH: EXEC SQL CLOSE C1;

As I have mentioned in the beginning there might be an easier way to do the
same in sqlplus. So, check other options before using this. Hope this might
help. Good luck!
-- 
Gorur Ravi (ravi)   uunet!pds3!ravi
Project Design Systems, Inc., 2231 Crystal Drive, Suite 1114,
Arlington, VA 22202