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.
Cet exercice fait partie du cours
Transactions and Error Handling in PostgreSQL
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'.
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de 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';