Can I update 2 Files with a SIngle Query in DB2

Question

I have 2 files , File A and File B (which has the same Keys). I need to update some fields in File A with some corresponding fields in File B. Also, If I am able to update the row in File A, then I need to update the field 'ERrorflag' in File B to a 'N'.

My question is , is there a way to achieve this in one shot without a Cursor? I know 'Update' can only update one file but is there any other options like 'Merge'?

Consider if triggers are a solution. Insert/Update/Delete on one table can trigger an action on the other table. — mao
– mao, Commented Nov 18, 2017 at 11:44
Why do you think you need to update two files in a single statement? What is the actual problem that you're trying to solve? — mustaccio
– mustaccio, Commented Nov 19, 2017 at 19:02

jmarkmurphy · Accepted Answer · 2017-11-19 01:47:50Z

1

So you can do this with a stored procedure call (but the stored procedure would have to perform two updates, one for each file), or with a trigger (The update on File A would trigger an update on File B (still two update statements, but the second one happens in the background). Unfortunately with the trigger option, any update to File A would cause the trigger to fire so if you only want the flag in File B to be updated when File A is updated with data from File B, you are going to have to determine how to prevent File B from being updated when any other update occurs. This is much easier to manage with a stored procedure, and you can also wrap the two updates in a transaction to make sure both occur or neither occurs.

answered Nov 19, 2017 at 1:47

jmarkmurphy

11.6k37 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can I update 2 Files with a SIngle Query in DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related