I recently experienced an issue when performing an update using a FOR OPENXML join where only two of the 4 columns in the update statement for the row were actually updated and the other 2 were not.
The update was statement resembled the following:
SET COLUMNA = rtrim(COLUMNB),
WHEN 'D' THEN 'Y'
COLUMNB = 'T' ,
COLUMND = UpdRec.Date
FROM OPENXML(@hDoc, '/UpdRecs/UpdateRec')
WITH ( ID1 char(02),
Date datetime) UpdRec
WHERE COLUMNB <> 'T'
AND COLUMNE= UpdRec.ID1
AND COLUMNF= UpdRec.ID2
87 UpdateRec Nodes were sent. Only on 1 row was the update incomplete.
On this row, COLUMNC and COLUMND were updated, but COLUMNA and COLUMNB were not. There initial values were not different from many of the other records updated during the transaction. I can verify it was indeed updated during the transaction based on the Date value that was assigned to COLUMND and the value assigned to COLUMNC
Has anyone encountered such an issue or see anything in my statement that would cause an issue??