Updating row in table using for each statement 2

[proteome]

Is it possible to update a table using some kind of for each statement??

I would like to create a column that contains the results from a parsing procedure that depends on another column in the same table. Something like the following pseudocode:

FOR EACH Row in tableA
index1 = InStr(1, file, "/")
index2 = Instr((index1 + 1), file, ".")
difference = index2-index1-1
Update tableA Set parse = Mid(file, index1+1, difference)
End FOR EACH

The object of the code would be to parse every file entry in the table and update the same table by inserting the parsed data into another tuple.

 

[M8KWR]

you could use an update query to do this for you.

 

[proteome]

I understand that the update function would work but how do I incorporate the above method ( ie instr and mid) into the sql statement without recieving an error

 

[M8KWR]

can you give me some example data i could use from the table...

 

[proteome]

FOR EACH Row in tableA
index1 = InStr(1, file, "/")
index2 = Instr((index1 + 1), file, ".")
difference = index2-index1-1
Update tableA Set parse = Mid(file, index1+1, difference)
End FOR EACH


columns in tableA
file data parse

in this case parse would be emply until the above code was run(somehow through an sql Update table script using the above code as a function??)

example data from tableA
file data parse
sample12/f12.1203 digest
sample12/f12.3234 digest

after running the update you would see
tableA
strSQL = UPDATE tableA set parse = some function
file data parse
sample12/f12.1203 digest f12
sample12/f12.3234 digest f12


thanks for the help

 

[lespaul]

try this:

select Mid(file, InStr(1, file, "/")+1, InStr(InStr(1, file, "/"), file, ".")-1) from TableA

if this returns the information that you need then you can update:

UPDATE TableA SET Parse = Mid(file, InStr(1, file, "/")+1, InStr(InStr(1, file, "/")+1, file, ".")-1)

Leslie

 

[PHV]

UPDATE TableA
SET Parse=Mid(file, InStr(1,file,"/")+1, InStr(1+InStr(1,file,"/"),file,".")-InStr(1,file,"/")-1)
WHERE file Like "*/*.*"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[proteome]

Thanks very much both of you, with help from you and another programmer here on tektips I finally got it to work, I decided to use a OleDBConnection and then used dataadapters and datasets to handle this problem using a for each row statement followed by an adapter update using the commandbuilder from .NET again thanks for taking the time. I do not know why I did not think of stringing the functions together (instr, mid), it makes sense thanks for the posts