Mass update using another table

Hi ​@Fluffy 

These might help you

Mass Update for multiple rows | IFS Community

Hierarchy select with mass update | IFS Community

Conditionally execute perform_batch XML | IFS Community

Cheers!

Thanx for the response.

Those dont answer my question.

​@Fluffy 

Try changing the select to be the contract, not the contract link.

You do not need to provide the ID as XPath, it is derived from the select itself.

Cheers!

If I do that, it just returns the contract_id twice. The problem is I need the linked contract id. The contract table will only return the contract id.

​@Fluffy 

According to your join constraint, it is the same value.

      <constraint>
        <left_operand>contract.contract_id</left_operand>
        <operator>equi</operator>
        <right_operand>c_contract_link.contract_id</right_operand>
      </constraint>

And c_contract_link is used for the where constraint.

Not sure what the problem you are facing is.

Cheers!

​@Shneor Cheshin

Im just not sure how I should format the hierarchy select to get the linked contract id from the c_contract_link table so that I can mass update the contract id according to the results of that select. I want to use this in a BR triggered by the contract table.

​@Fluffy 

Try the following (Did not run this, so might be some syntax issues)

<mass_update_contract>
  <hierarchy_select>
    <primary_table>contract</primary_table>
    <attrs>
      <attr>contract.contract_id</attr>
    </attrs>
    <from>
      <table>contract</table>
      <table>c_contract_link</table>
    </from>
    <where>
      <data_constraint>
        <constraint>
          <left_operand>c_contract_link.cancel_with_primary</left_operand>
          <operator>eq</operator>
          <right_operand>Y</right_operand>
        </constraint>
        <constraint>
          <left_operand>contract.contract_id</left_operand>
          <operator>eq</operator>
          <right_operand>@contract_id</right_operand>
        </constraint>
      </data_constraint>
      <join_constraint>
        <constraint>
          <left_operand>contract.contract_id</left_operand>
          <operator>equi</operator>
          <right_operand>c_contract_link.contract_id</right_operand>
        </constraint>
      </join_constraint>
    </where>
  </hierarchy_select>
  <contract>
    <user_def5>@user_def5</user_def5>
    <contract_status>CANCEL</contract_status>
    <contract_version>1</contract_version>
    <cancelled_dt>@cancelled_dt</cancelled_dt>
    <update />
  </contract>
  <transaction_size>1</transaction_size>
  <synchronous>Y</synchronous>
</mass_update_contract>

Cheers!

​@Shneor Cheshin 

This simply updates the primary contract, the one that takes the @contract_id value.

Is there a way to maybe use mass insert but force it to update instead perhaps?

Ok, I found a solution that seems to work. I have to use perform_mass_insert instead:

<perform_mass_insert>
  <parameters>
    <select>
     <hierarchy_select>
  <primary_table>c_contract_link</primary_table>
  <attrs>
    <attr>c_contract_link.linked_contract_id</attr>
  </attrs>
  <from>
    <table>contract</table>
  </from>
  <where>
    <data_constraint>
      <constraint>
        <left_operand>c_contract_link.contract_id</left_operand>
        <operator>eq</operator>
        <right_operand>@contract_id</right_operand>
      </constraint>
      <constraint>
        <left_operand>c_contract_link.cancel_with_primary</left_operand>
        <operator>eq</operator>
        <right_operand>Y</right_operand>
      </constraint>
    </data_constraint>
    <join_constraint>
      <constraint>
        <left_operand>contract.contract_id</left_operand>
        <operator>equi</operator>
        <right_operand>c_contract_link.contract_id</right_operand>
      </constraint>
    </join_constraint>
  </where>
</hierarchy_select>
    </select>
    <update>
      <update_contract>
        <contract>
          <contract_id xpath="linked_contract_id" />
          <contract_version>1</contract_version>
          <user_def5>@user_def5</user_def5>
          <contract_status>CANCEL</contract_status>
          <cancelled_dt>@cancelled_dt</cancelled_dt>
          <insert_update />
        </contract>
      </update_contract>
    </update>
  </parameters>
</perform_mass_insert>

​@Fluffy 

• That is exactly what your original XML is doing.
• Mass Insert is not a baseline MPM. Unless you have a customisation for that, it will not work.

Cheers!

Ah yea its a customisation. Sorry. Wont work for other people.

But for other people, could someone post an example of a working script for mass update that pulls data from one table and uses the results to update another table?