Notes on updating & deleting data using SQL.

Update Data

Update a Row

/* schema */
CREATE TABLE fleet (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name TEXT,
  class TEXT,
  length INTEGER
);

INSERT INTO fleet(name, class, length) VALUES ('a-wing', 'fighter', 7);
INSERT INTO fleet(name, class, length) VALUES ('cruiser', 'corvette', 127);
INSERT INTO fleet(name, class, length) VALUES ('x-wing', 'fighter', 13);
INSERT INTO fleet(name, class, length) VALUES ('y-wing', 'bomber', 23);
<br/>
/* updating data */
UPDATE fleet 
SET length = 8 
WHERE id = 1;

Update Multiple Rows

UPDATE fleet 
SET length = 10 
WHERE name LIKE "%wing%"
LIMIT 2;

Delete Data

DELETE 
FROM fleet 
WHERE id = 1;

Update Table

Add a new column

ALTER TABLE fleet 
ADD mass INTEGER;

UPDATE fleet 
SET mass = 6 
WHERE id = 1;

Set a default value for a new column

ALTER TABLE fleet 
ADD mass INTEGER
default 10;

UPDATE fleet 
SET mass = 6 
WHERE id = 1;

Delete Table

DROP TABLE fleet;

Transaction

When we issue a SQL command that changes the database, it starts a transaction. A sequence of operations treated as a single logical piece of work. A transaction must comply to the ACID principles.

Whenever we issue a command like CREATE, UPDATE, INSERT, or DELETE, we are automatically starting a transaction. We can also wrap multiple commands inside a bigger transaction.

START TRANSACTION;

SELECT @myclass:=class 
FROM fleet 
WHERE name = 'a-wing';
<br/>
UPDATE fleet
SET class = @myclass;
<br/>
COMMIT;

Transaction are useful when we require all our commands operate on the same data state. A problem can occur when you execute a sequence of events. Half-way through, another use executs a command that changes the data your working on. Thus, half your commands executed on 1 state of data, the other half on a 2nd state of data. A transaction prevents this from happening.

Rollback

We can undo a transaction using the ROLLBACK command. This can only be done when a transaction hasn't been committed. By default, transactions are committed automatically so you will 1^st need to turn this off. Note, we are referring to the MySQL engine in this example. Other engines may differ.

/* disable committing transaction by default */
SET autocommit = 0;

UPDATE fleet SET length = 100 WHERE id = 1;
<br/>
ROLLBACK;

Playground

• DB Fiddle - SQL Database Playground