updatexml for particular rows only

Question

Context: I want to increase the allowance value of some employees from £1875 to £7500, and update their balance to be £7500 minus whatever they have currently used.

My Update statement works for one employee at a time, but I need to update around 200 records, out of a table containing about 6000.

I am struggling to workout how to modify the below to update more than one record, but only the 200 records I need to update.

UPDATE employeeaccounts 
SET    xml = To_clob(Updatexml(Xmltype(xml), 
                 '/EmployeeAccount/CurrentAllowance/text()',187500, 
                 '/EmployeeAccount/AllowanceBalance/text()', 
                 750000 - (SELECT Extractvalue(Xmltype(xml), 
                 '/EmployeeAccount/AllowanceBalance', 
   'xmlns:ts=\"http://schemas.com/\", xmlns:xt=\"http://schemas.com\"' 
   ) 
      FROM   employeeaccounts 
      WHERE  id = '123456'))) 
WHERE  id = '123456'

Example of xml column (stored as clob) that I want to update. Table has column ID that hold PK of employees ID EG 123456

<EmployeeAccount>
 <LastUpdated>2016-06-03T09:26:38+01:00</LastUpdated>
 <MajorVersion>1</MajorVersion>
 <MinorVersion>2</MinorVersion>
 <EmployeeID>123456</EmployeeID>
 <CurrencyID>GBP</CurrencyID>
 <CurrentAllowance>187500</CurrentAllowance>
 <AllowanceBalance>100000</AllowanceBalance>
 <EarnedDiscount>0.0</EarnedDiscount>
 <NormalDiscount>0.0</NormalDiscount>
 <AccountCreditLimit>0</AccountCreditLimit>
 <AccountBalance>0</AccountBalance>
</EmployeeAccount>

How are the 200 you want to update identified? And can you add some sample data to the question? — Alex Poole, Jun 03 '16 at 09:19
As it is only 200, which I have been sent via excel, I am hoping just to do a where id in (123456, 654321, etc — DanBot, Jun 03 '16 at 09:26
So really the issue is how to correlate the old account balance you're getting in the subquery, with the row being updated - since you can't just put the same in list in both? — Alex Poole, Jun 03 '16 at 09:39

score 0 · Accepted Answer · answered Jun 03 '16 at 09:42

You don't need a subquery to get the old balance, you can use the value from the current row; which means you don't need to correlate that subquery and can just use an in() in the main statement:

UPDATE employeeaccounts 
SET    xml = To_clob(Updatexml(Xmltype(xml), 
                 '/EmployeeAccount/CurrentAllowance/text()',187500, 
                 '/EmployeeAccount/AllowanceBalance/text()', 
                 750000 - Extractvalue(Xmltype(xml), 
                   '/EmployeeAccount/AllowanceBalance', 
                   'xmlns:ts=\"http://schemas.com/\", xmlns:xt=\"http://schemas.com\"')
    )) 
WHERE  id in (123456, 654321, ...);

Yes Alex exactly right, fear I was just overcomplicating it! — DanBot, Jun 03 '16 at 09:55

updatexml for particular rows only

1 Answers1