Multiple statement transactions
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. Flag fields like this are common in government data sets, and are great for categorizing records.
This exercise is part of the course
Transactions and Error Handling in PostgreSQL
Exercise instructions
- Begin a transaction.
- Build 3 update statements to detect each condition in the column and set the
FIELD48
flag to the proper value. - End the transaction.
- Select a count of records where the flag is set to
'BOTH'
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Begin a new transaction
___;
-- 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 ___ = 'FOREIGN'
WHERE RCON2236 > 0;
-- Update FIELD48 flag status if US State Government and Foreign deposits are held
UPDATE ffiec_reci
SET ___ = 'BOTH'
WHERE RCON2203 > 0
AND RCON2236 > 0;
-- Commit the transaction
___;
-- Select a count of records where FIELD48 is now BOTH
SELECT COUNT(FIELD48)
FROM ffiec_reci
WHERE ___ = '___';