CODEWITHSUNDEEP
sqlibm-midrangedb2-400
AS400 User
AS400 User

Reputation: 187

Can I update 2 Files with a SIngle Query in DB2

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'?

Upvotes: 0

Views: 308

Answers (1)

jmarkmurphy
jmarkmurphy

Reputation: 11493

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.

Upvotes: 1

Related Questions