Left Arrow.Back

SQL - Update / Delete Data

3 rectangles stacked on top of 1 another.

Notes on updating & deleting data using SQL.

Last Tended-
PlantedNov 2021
StatusSeed
TagsData
3 squares in a row, 1 faded. A circle above the faded square.

Update Data

Update a Row

schema.sql

output

schema.sql

fleet

idnameclasslength
1a-wingfighter8
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23

Update Multiple Rows

schema.sql

output

schema.sql

fleet

idnameclasslength
1a-wingfighter10
2cruisercorvette127
3x-wingfighter10
4y-wingbomber23
Source
3 squares in a row, 1 faded.

Delete Data

schema.sql

output

schema.sql

fleet

idnameclasslength
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23
Source
3 large squares in a row, 1 faded. 1 square above the faded square.

Update Table

Add a new column

schema.sql

output

schema.sql

fleet

idnameclasslengthmass
1a-wingfighter76
2cruisercorvette127null
3x-wingfighter13null
4y-wingbomber23null

Set a default value for a new column

schema.sql

output

schema.sql

fleet

idnameclasslengthmass
1a-wingfighter76
2cruisercorvette12710
3x-wingfighter1310
4y-wingbomber2310
3 large squares in a row, 1 faded.

Delete Table

schema.sql

output

schema.sql

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

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.

schema.sql

output

schema.sql

fleet

idnameclasslength
1a-wingfighter7
2cruiserfighter127
3x-wingfighter13
4y-wingfighter23

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.

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. By default, transactions are committed automatically so you will 1st need to turn this off. Note, we are referring to the MySQL engine in this example. Other engines may differ.

schema.sql

output

schema.sql

fleet

idnameclasslength
1a-wingfighter7
2cruisercorvette127
3x-wingfighter13
4y-wingbomber23
A computer screen with a blinking cursor.

Playground