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.

Este exercício faz parte do curso

Transactions and Error Handling in PostgreSQL

Ver curso

Instruções do exercício

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

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

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

Editar e executar o código