x

how to parse through table and update certain values using either cursors or any other method

Hi All,

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.

DECLARE @mylog_mgo float declare @mybt_mgo float

DECLARE @mycursor CURSOR SET @mycursor = CURSOR FOR SELECT LOG_MGO, MGO FROM inc_trial.dbo.INC_UPDATED where ROCK_TYPE = 0 for update of MGO OPEN @mycursor

FETCH NEXT FROM @mycursor INTO @mylog_mgo, @mybt_mgo WHILE @@FETCH_STATUS = 0 BEGIN update inc_trial.dbo.INC_UPDATED

set MGO = convert(varchar,EXP(@mylog_mgo)) print @mylog_mgo print @mybt_mgo print convert(varchar,EXP(@mylog_mgo))

FETCH NEXT

FROM @mycursor INTO @mylog_mgo, @mybt_mgo

END CLOSE @mycursor DEALLOCATE @mycursor GO


the results

(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

3.17085

more ▼

asked Jan 20, 2010 at 06:18 AM in Default

avatar image

Michael
13 1 1 3

Please mark your SQL code and press the "Code sample" button. It will be much easier to read and in the end help you.

Jan 20, 2010 at 06:35 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

UPDATE inc_trial.dbo.INC_UPDATED
SET MGO = convert(varchar,EXP(LOG_MGO))
WHERE ROCK_TYPE = 0

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.

UPDATE inc_trial.dbo.INC_UPDATED
SET MGO = convert(varchar,EXP(LOG_MGO))

    AL203 = convert(varchar,exp(LOG_AL203))
WHERE ROCK_TYPE = 0

more ▼

answered Jan 20, 2010 at 02:26 PM

avatar image

user-974
46 2 2

(comments are locked)
10|1200 characters needed characters left

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.

UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;

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

more ▼

answered Jan 20, 2010 at 06:41 AM

avatar image

Håkan Winther
16.5k 36 45 57

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x68

asked: Jan 20, 2010 at 06:18 AM

Seen: 1845 times

Last Updated: Jan 20, 2010 at 07:32 AM

Copyright 2016 Redgate Software. Privacy Policy