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.

Cet exercice fait partie du cours

Transactions and Error Handling in PostgreSQL

Afficher le cours

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

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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 ___ = '___';

Modifier et exécuter le code