I have the following table. i_updated which has the following data.
location, rock_type, CO , AL203, MGO, CAO , LOG_CO, LOG_AL203, LOG_MGO, LOG_CAO
1 0 1.0 1.0 3.0 1.0 0.3 0.4 0.72 0.73
1 20 0 0 3.0 1.0 0.4545 0.56 0.6565 0.66345
1 30 1.0 1.0 3.0 1.0 0.454 0.5656 0.56565 0.7665
i need to write an sql cursor or query that will parse the data on row 1 then replace the contents of CO from 1.0 to 1.34 and AL203 from 1.0 to 1.49 and MGO from 3 to 20.08 and CAO from 1.0 to 2.075
then parse the next row to replace the values of CO, AL203, MGO, CAO
the computation of the values above for row 1 are
CO = exp(.3) or exp(LOG_CO) which is 1.34
AL203 = exp(.4) or exp(LOG_AL203) which is 1.49
MGO = exp(.72) or exp(LOG_MGO) which is 20.08
CAO = exp(.73) or exp(LOG_CAO) which is 2.075
I have already done some coding using cursor and trying only to update the mgo field but the code seems to loop getting the value at the bottom depending on the rock_type . essentially all the mgo values for all 51 rows are now 3.17085 .
Thanks for listening to my problem.
(51 row(s) affected) 0.889 3.17085 2.4327
(51 row(s) affected) 0.683 2.4327 1.97981
(51 row(s) affected) 0.432 1.97981 1.54034
(51 row(s) affected) 0.185 1.54034 1.20322
(51 row(s) affected) 1.154 1.20322
The entire block of code can be simply replaced with an update statement. Using your sample code above, it will correctly function if you replace it with:
Similar statements can be used for all of the other columns that need updating, and they can all be combined into one statement, e.g.
answered Jan 20, 2010 at 02:26 PM
I haven't reviewed all your code because of the lack of formatting, but you need to use a where clause in your update, and you should use "WHERE CURRENT OF", see sample below.
Every row in your tables causes a loop in the cursor, and every loop updates all the rows with the current values of the cursor, so every row will be updated to the values of the last row.
...but I don't think you need a cursor at all. If I where you I would try to use a single UPDATE statement. If all your data for the update of the row exists in the same row, then the cursor is overkill. I always tries to avoid cursors, and I think that cursors are only needed in rare cases.