3 rectangles stacked on top of 1 another.

SQL - Update / Delete Data

Tended: Dec 2021

Status: decay

3 squares in a row, 1 faded. A circle above the faded square.

Update Data

Update a Row

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

UPDATE fleet 
SET length = 8 
WHERE name = 'a-wing';

SELECT *
FROM fleet;

Update Multiple Rows

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

UPDATE fleet
SET length = 8 
WHERE name LIKE '%wing%';

SELECT *
FROM fleet;

Update a Row (& return modified row)

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

UPDATE fleet
SET length = 8 
WHERE name = 'a-wing'
RETURNING *;

Delete Data

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

DELETE FROM fleet 
WHERE name = 'a-wing';

SELECT *
FROM fleet;

2 groups of shapes stacked vertically. 3 squares, 1 faded. 3 squares, 1 faded, a circle above the faded. A square and a circle to the right of the groups.

Transaction

A transaction bundles multiple steps into a single, all-or-nothing operation. If some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. The intermediate states between the steps are not visible to other concurrent transactions. Note, the transaction being referred to here is also known as a transaction block. Technically, a single database operation (such as delete a row) is also classified as a transaction.

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

-- a transaction with 2 steps
BEGIN;
UPDATE fleet
    SET length = length + 1
    WHERE name = 'a-wing';
DELETE FROM fleet 
WHERE name = 'x-wing';
COMMIT;

SELECT *
FROM fleet;

3 groups of shapes (3 squares, 2 squares and a circle, 3 squares). Shades become more faded from right to left.

Rollback

We can undo a transaction using the ROLLBACK command. This can only be done when a transaction hasn't been committed.

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

BEGIN;
UPDATE fleet
    SET length = length + 1
    WHERE name = 'a-wing';
DELETE FROM fleet
WHERE name = 'x-wing';
ROLLBACK;

SELECT *
FROM fleet;

3 large squares in a row, 1 faded. 1 square above the faded square.

Update a Table

Add a new column

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

ALTER TABLE fleet 
ADD COLUMN mass INTEGER
-- setting a default value (optional)
DEFAULT 10;

SELECT *
FROM fleet;

Remove a column

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

ALTER TABLE fleet
DROP COLUMN length;

SELECT *
FROM fleet;

Change a column's name

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

ALTER TABLE fleet 
RENAME COLUMN name TO ship_name;

SELECT *
FROM fleet;

Delete a Table

result

CREATE TABLE IF NOT EXISTS fleet (
    id serial PRIMARY KEY,
    name VARCHAR (50) NOT NULL,
    class VARCHAR (50) NOT NULL,
    length INTEGER NOT NULL
);

INSERT INTO fleet(name, class, length)
VALUES ('a-wing', 'fighter', 7),
       ('cruiser', 'corvette', 127),
       ('x-wing', 'fighter', 13),
       ('y-wing', 'bomber', 23);

CREATE TABLE IF NOT EXISTS personnel (
  id serial PRIMARY KEY,
  name VARCHAR (50) NOT NULL,
  starship INTEGER,
  related_to INTEGER
);

INSERT INTO personnel(name, starship, related_to)
VALUES ('Luke', 3, 3),
       ('Obi-Wan', 1, null),
       ('Leia', null, 1);

DROP TABLE IF EXISTS fleet;

SELECT table_name
FROM information_schema.tables;