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.

Deze oefening maakt deel uit van de cursus

Transactions and Error Handling in PostgreSQL

Cursus bekijken

Oefeninstructies

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

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

-- 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 bewerken en uitvoeren