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.

Diese Übung ist Teil des Kurses

Transactions and Error Handling in PostgreSQL

Kurs anzeigen

Anleitung zur Übung

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

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

-- 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 ___ = '___';

Code bearbeiten und ausführen