Multistatement Rollbacks
Now let's use multiple statements in a transaction to set a flag in FIELD48 based on if it holds US state government assets represented in RCON2203, foreign assets represented in RCON2236, or both. The values for FIELD48 should be 'US-STATE-GOV', 'FOREIGN', or 'BOTH' respectively. However, You've made a mistake in the statement for both.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
- Build 3 update statements to detect each condition and set the flag.
- Undo the mistake of setting it to
'BOOTH'instead of'BOTH'by rolling back the transaction. - Select a count of records where the flag is set to
'BOOTH'.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Begin a new transaction
BEGIN;
-- Update FIELD48 flag status if US State Government deposits are held
UPDATE ffiec_reci
SET ___ = 'US-STATE-GOV'
WHERE RCON2203 > 0;
-- Update FIELD48 flag status if Foreign deposits are held
UPDATE ffiec_reci
SET FIELD48 = '___'
WHERE RCON2236 > 0;
-- Update FIELD48 flag status if US State Government and Foreign deposits are held
UPDATE ffiec_reci
SET FIELD48 = 'BOOTH'
WHERE RCON2236 > 0
AND ___ > 0;
-- Undo the mistake
___;
-- Select a count of records that are booth (it should be 0)
SELECT COUNT(FIELD48)
FROM ffiec_reci
WHERE ___ = 'BOOTH';