Sql-server – How to loop through all table rows and perform subquery using values of same row

sql serversql-server-2012updatexml

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

Best Answer

Answer based on comments originally left by Akina

UPDATE table 
SET valA = xml.value(_parsing queryA_), 
    valB = xml.value(_parsing queryB_)

When you use a column name in the right-hand side of an assignment expression (parsing expression in your case) it is referenced to a column value in the same record that is currently being updated (if you want to think about an update like it is a record-by-record process). It is always the same row field. Referencing a column in another row is always more complex (needs a window function or another reference to the table).

To continue processing a partially-updated you could add WHERE reference > last_ref to the SET clause of the UPDATE. Your WHERE clause will determine whether each record will be updated or not.

A better way may be to check valA and/or valB values for NULL - if not then the record was already processed previously. For example UPDATE ... WHERE valA IS NULL (an index on the tested field will help). If it is possible that some of the columns will remain null after processing, it is a good idea to set it to some non-NULL value (empty string or single space, for example).

Related Question