Alterations and Transactions

Jed Rembold

Monday, October 13, 2025

Announcements

  • Homework 6 due on Wednesday
    • On GROUP BY, so you have everything you need
  • HW4 feedback went out, and I’m working on HW5 now to get caught up!
  • Note that we are largely skipping Chapter 11, so we’ll be looking at Ch 12 material on Wednesday
  • Terminal psql issues? I posted something on Discord, but will summarize.
  • Polling today: polling.jedrembold.prof

Continuing Alterations

Multi-Updates

  • Sometimes you want to update several things at once
  • So long as they are all in the same table, you can do this with a single UPDATE statement
  • After the SET keyword, either:
    • Separate each assignment by a comma: SET |||col₁||| = 5, |||col₂||| = |||col₃|||
    • Pair up the assignments with parentheses: SET (|||col₁|||, |||col₂|||) = (5, |||col₃|||)
      • They are then assigned by order

Understanding Check

The table named revq to the right is acted upon by the below SQL queries. What entries in the table are left untouched once all queries have been run?

row1row2row3TEXTREALINTA0.2415B9.14C410ABCD 

ALTER TABLE revq ADD COLUMN row4 INT;
UPDATE revq SET row4 = row2;
UPDATE revq SET (row2,row3)=(row3, row2) WHERE row1 IN ('B','C');
UPDATE revq SET row3 = row3 - row4;
UPDATE revq SET row2 = row2 + row4 WHERE row3 > 10;
ALTER TABLE revq DROP COLUMN row4;

Backup Tables

  • Frequently, if you are about to heavily modify a table, you should consider working on a backup copy

  • We actually have already seen the basic machinery for this:

    CREATE TABLE |||new_table||| AS
SELECT * FROM |||og_table|||;
    • Note: Indexes and constraints are stored separately, and so are NOT copied over using this process!

  • For including constraints and indexes, you can use a Postgres specific syntax, but the newly created table will initially be missing the data

    CREATE TABLE |||new_table|||
(LIKE |||og_table||| INCLUDING ALL);

Table to Table

  • In some cases, you’ll want to update or pass information across tables

    • Maybe one table has newer values that you want to use to update the original table

  • In core SQL, you’d need to use subqueries, which we’ll be talking about in a few chapters

  • In Postgres, to update, you can use FROM:

    UPDATE |||table₁|||
SET |||col_name||| = |||table₂|||.|||col₂|||
FROM |||table₂|||
WHERE |||table₁|||.|||col₁||| = |||table₂|||.|||col₁|||;

  • To insert values from another table into another:

    INSERT INTO |||new_table||| SELECT * FROM |||old_table|||;

Deletions

  • Similar to changing tables, removing things from tables has two main keywords:
    • DROP for removing structural aspects of a table like columns, constraints, indexes, or the table itself
    • DELETE FROM for removing content (rows) from tables
  • DROP will frequently come after an ALTER TABLE unless you are dropping the table itself
  • DELETE FROM without a filter will delete all rows
    • Make absolutely sure you are using a filter if you don’t want that to happen!
    • Another good reason to back up your tables before editing them

Getting Deleted

ALTER TABLE |||table name||| DROP COLUMN |||col_name|||;
ALTER TABLE |||table name||| DROP CONSTRAINT |||const_name|||;
DROP INDEX |||index_name|||;
DROP TABLE |||table name|||;

DELETE FROM |||table name|||; -- All rows gone!
DELETE FROM |||table name||| WHERE |||condition|||;
  • In general, unless you have an important reason, don’t remove actual data from a table
    • You can filter it, you can create new tables that are missing that data, etc.

ACID Transactions

Transactions

  • Atomicity is an important aspect of most database changes
    • The idea that related changes should happen in a single, self-contained step
  • Many changes you might make to a database have several steps though!
    • Need to change one value in one table and another value in another table
    • Need to create a new row and then copy some information into it
  • Remember that, in general, others can access the database at the same time
    • What if they tried to access the data you were working on mid-operation?
  • So solve these issues, SQL has the concept of a transaction

Bundling Up

  • A transaction is essentially a bundling of several statements into one, discrete change to the database
  • Commands within the transaction have not yet modified the database, but exist only in local memory
  • Changes get written to the database all at once upon the conclusion of the transaction
  • Starting a transaction?
    • START TRANSACTION; or BEGIN;
  • Ending a transaction?
    • COMMIT; actually makes the changes
    • ROLLBACK; throws out everything within the transaction

Uses of Transactions

  • Protecting against system faults
    • What if you have a system crash in the middle of an operation?
      • What commands had been run? What commands had not?
    • Transactions actually write to a log what they are going to do before they actually do it. So in case of a crash, then the transaction can then simply be rerun
  • Protecting against simultaneous access
    • Changes occur all at once, so it is impossible for another database user to access data “mid-change”
    • Other users of the database will see none of your changes until actually committed
  • Testing changes
    • Sometimes it is useful to check to see that some changes look the way you wanted before actually changing the database
    • Embedding within a transaction block always gives you the option to rollback

Presidential Cleaning

Practice Activity

  • There is a simple CSV of presidents and debt here
  • It has some data consistency problems which you should determine and fix before answering the following questions:
    • What are the top 5 presidents to have the greatest average annual increase in national debt over the years of their presidency?
    • How do the median values of annual increases in national debt compare across party lines?
    • Trickier: What is the average change in the annual increase percentage of national debt overall all the years?

Groups

  • You’ll be working in small groups, only 1 computer interacting with a database
    • Others can have slides, documentation, etc. open
  • Groups:
    • Group 1: Saul, Lola, Evan
    • Group 2: Catalina, Thomas, Michael
    • Group 3: Talia, Evyn, Mia
    • Group 4: Jude, Julia, Beyla
    • Group 5: Dominic, Addison, Gregory
    • Group 6: Kara, KJ, Meghla
    • Group 7: Salvador, Farley, Jimmy
    • Group 8: Jose, Aaron, Oliver
    • Group 9: Judah, Cody, Finn
    • Group 10: Jackson, Sophie, Emma
    • Group 11: Cecil, Emma, Nico
    • Group 12: Connor, Tegan
Group Locations

Question Answers

  • What are the top 5 presidents to have the greatest average annual increase in national debt over the years of their presidency?
    • Reagan, HW Bush, Ford, Carter, Obama
  • How do the median values of annual increases in national debt compare across party lines?
    • Democrats: 3.65%
    • Republicans: 7.40%
  • Trickier: What is the average change in the annual increase percentage of national debt overall all the years?
    • Only about 0.05%, but seemingly a slight steady increase
// reveal.js plugins // Added plugins