I have a
table structured as follows:
| reference | valA | valB | xml | 1 NULL NULL <xml stuff> 3 NULL NULL <xml stuff> 4 NULL NULL <xml stuff> 6 NULL NULL <xml stuff>
My objective is to parse the
xml column and insert the values into columns
valB. I have a query which does this but I only know how to do this by specifying the reference number of the row:
UPDATE table SET valA = (SELECT xml.value(_parsing queryA_) FROM table WHERE reference = N), valB = (SELECT xml.value(_parsing queryB_) FROM table WHERE reference = N) WHERE reference = N;
I need to be able to loop through each row in the table and perform the same query in each row based on values in that row. So the pseudo code for this would be:
UPDATE table SET valA = (SELECT xml.value(_parsing queryA_) FROM current row), valB = (SELECT xml.value(_parsing queryB_) FROM current row) in current row;
I am currently doing this in an awkward way by implementing this loop in my main program (C++) by first reading in each reference in an array and then looping through the array for the reference number and setting it in the query in place of
N in the example query above.
I am very new to SQL so I don't know if I am making this more complicated than it is and if there's an easy way to do it.
I have been running this operation for many hours (about 164,000 rows in table) so I have a partially updated table.