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

Michael gravatar image

Michael
13 1 1 1

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

user-974 gravatar image

user-974
46 1

Hi Brendt,

Thanks will try the code when i get to the office. But in your code above will it try the first row if ROCK_TYPE = 0 then move to the next row to try if 2nd row is ROCK_TYPE = 0 until it goes to the end? Im sorry my SQL programming is somewhat just basic.

Thanks.
Jan 22, 2010 at 04:07 AM Michael
(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

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x58

asked: Jan 20, 2010 at 06:18 AM

Seen: 1553 times

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