Left Arrow.Back

SQL - Update / Delete Data

Table Of Contents

3 rectangles stacked on top of 1 another.

How to update & delete data in SQL (Postgres), with interactive examples.

Last Tended
Planted
StatusSeed
3 squares in a row, 1 faded. A circle above the faded square.

Update Data

Update a Row

/query.sql

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

/query.sql

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)

/query.sql

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 *;
3 squares in a row, 1 faded.

Delete Data

/query.sql

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

/query.sql

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

/query.sql

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

/query.sql

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

/query.sql

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

/query.sql

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;
3 large squares in a row, 1 faded.

Delete a Table

/query.sql

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;

Playground

Source