Simplified
Simplified

L2.1Changing a value in a row

YOU

Changing a value in a row

usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
UPDATE
changes values in rows that already exist
UPDATEusersSET
UPDATE users SET xp = xp + 10WHERE id = 1;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
SET xp = xp + 10namexpAda120 → 130SET xp = 10namexpAda120 → 10
UPDATE users SET name = 'Dee K',email = 'dee.codes@gmail.com'WHERE id = 4;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
UPDATE profiles SET bio = 'Building my first app'WHERE id = 205;profilesidusernamebio204maya.devDesigner in LA205sam.codesLearning to code206lee.devCoffee first
UPDATE posts SET likes = likes + 1WHERE id = 81;postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day12
UPDATE usersSET xp = xp + 10WHERE id = 1;usersidemailnamexpcreated_at1ada@simplified.orgAda120 → 1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

Databases · Lesson 2.1

Changing and Removing Rows

Changing a value in a row

Simplified
0:00 / 3:18

So INSERT added Gia's row to users, with 0 XP from xp's DEFAULT.

Now users holds seven learners, from Ada, with id 1, to Gia, with id 7.

But a learner's XP doesn't stay where it started.

Say Ada, who has 120 XP, finishes a lesson worth 10 XP.

usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

So how does Simplified's server change the 120 in Ada's row?

An INSERT would add a second row for Ada, not change the one she has.

So the server sends a query that starts with UPDATE.

UPDATE changes values in rows that already exist.

After it comes the table, users, then SET, with the column to change and its new value.

SET gives a column its new value, like xp equals xp plus 10.

xp plus 10 means: take the xp already in the row, and add 10.

Then a WHERE picks which rows to change: where the id is 1, Ada's id.

UPDATE users SET xp = xp + 10WHERE id = 1;table2new value3which rows

So what does Postgres do with this UPDATE?

UPDATE users SET xp = xp + 10WHERE id = 1;usersidemailnamexpcreated_at1ada@simplified.orgAda120 → 1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

Postgres goes through users one row at a time, checking the WHERE, as a SELECT does.

Ada's id is 1, so her row passes.

Her xp is 120, so xp + 10 is 130, and Postgres puts 130 in its place.

Ben's id is 2, so his row fails, and his 40 stays.

Every other row fails too, so only Ada's row changes.

And only her xp cell: her id, email and name stay as they were.

So why not just set xp to 130?

SET xp = xp + 10namexpAda120 → 130

Because then the server would first need to know Ada had 120.

With xp plus 10, Postgres reads the 120 itself, from Ada's row.

And what would setting xp to 10 do?

SET xp = xp + 10namexpAda120 → 130SET xp = 10namexpAda120 → 10

It would put 10 in, whatever was there.

So Ada would drop from 120 XP to 10.

And what if one change needs two columns?

Say Dee, whose id is 4, changes her name to Dee K, and her email to dee.codes@gmail.com.

UPDATE users SET name = 'Dee K',  email = 'dee.codes@gmail.com'WHERE id = 4;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

SET can change both, with a comma between them.

So the query sets name to Dee K, and email to dee.codes@gmail.com, where the id is 4.

Both cells in Dee's row change, and her id and XP stay.

So how might Instagram save your new bio?

Say Instagram keeps profiles in a table, one row for each account, with a bio column.

UPDATE profiles SET bio = 'Building my first app'WHERE id = 205;profilesidusernamebio204maya.devDesigner in LA205sam.codesBuilding my first app206lee.devCoffee first

Your account's id is 205, and your bio says "Learning to code".

You change it to "Building my first app", and tap Done.

So Instagram's server might send an UPDATE that sets bio to your new words, where the id is 205.

Only your row passes, so only your bio cell changes.

And how might a like count go up when you double-tap a post?

Say Instagram's posts table has a likes column, and the post's id is 81.

UPDATE posts SET likes = likes + 1WHERE id = 81;postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day12 → 13

The post has 12 likes, and you double-tap it.

So Instagram's server might send an UPDATE that sets likes to likes plus 1, where the id is 81.

Postgres takes the 12 already in the row, adds 1, and puts 13 in its place.

So the post now shows 13 likes.

So UPDATE changes the columns SET names, in every row that passes the WHERE.

Example: Changing a value in a row

Simplified's users table holds Fay, with id 6 and 75 XP. Fay finishes a lesson worth 20 XP, and Simplified's server sends Postgres UPDATE users SET xp = xp + 20 WHERE id = 6;.

What is Fay's xp afterwards?

Explanation

First, only Fay's row has id 6, so only her row passes the WHERE.

Then, xp + 20 takes the 75 already in her row and adds 20, which makes 95.

Therefore, Fay's xp is 95.

Question 1

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres UPDATE users SET xp = xp + 10 WHERE xp = 40;.

How many rows change?

rows

Taking a row out for good

usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
DELETE FROM
removes rows from a table
DELETE FROMusers
DELETE FROM users WHERE id = 3;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30SELECT COUNT(*)FROM users;count6SELECT * FROM usersWHERE email ='cy@simplified.org';0 rows
usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:308hal@simplified.orgHal02026-11-05 11:40
UPDATE users SET name = NULLWHERE id = 5;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
DELETE FROM posts WHERE id = 81;postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day13
DELETE FROM messages WHERE chat_id = 42;messagesidchat_idtext30142Hi Mom31142Where are you?…42…38057See you at 738163lol
DELETE FROM usersWHERE id = 3;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

Databases · Lesson 2.2

Changing and Removing Rows

Taking a row out for good

Simplified
0:00 / 2:56

So Ada's UPDATE changed her xp, from 120 to 130, and her row stayed in users.

Now say Cy decides to leave Simplified, and taps Delete account in settings.

Cy's row has id 3, and 0 XP.

usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

So how does Simplified's server take Cy's whole row out of users?

An UPDATE only changes the values in a row, so the row would still be there.

So the server sends a query that starts with DELETE FROM.

DELETE FROM removes rows from a table.

After it comes the table, users, then a WHERE that picks the rows to remove.

So the query is a DELETE FROM users, where the id is 3.

DELETE FROM users WHERE id = 3;table2which rows

So what does Postgres do with it?

DELETE FROM users WHERE id = 3;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

Postgres goes through users one row at a time, checking the WHERE.

Ada's id is 1, so her row fails, and stays.

Ben's id is 2, so his row stays too.

Cy's id is 3, so Cy's row passes, and Postgres removes it: every cell at once.

Every other row fails, so users now holds six rows.

So what do later queries see?

usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30SELECT COUNT(*)FROM users;count6SELECT * FROM usersWHERE email ='cy@simplified.org';0 rows

Counting the rows in users now gives 6, not 7.

And a SELECT for cy@simplified.org sends back an empty result: 0 rows.

The row is gone from every later query.

And what happens to Cy's id, 3?

Postgres never hands out the same id twice.

So if a new learner signed up next, their row would get 8.

That's the next after Gia's 7, not Cy's old 3.

That way, id 3 only ever meant Cy.

usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:308hal@simplified.orgHal02026-11-05 11:40

But what if only one cell should go, like a name?

Say Eli, whose id is 5, clears the name on the profile, which is optional.

DELETE removes whole rows, never one cell.

So the server sends an UPDATE instead, that sets name to NULL, where the id is 5.

Eli's row stays, with NULL in name.

UPDATE users SET name = NULLWHERE id = 5;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.org—402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30

So how might Instagram delete one of your posts?

Say your username is sam.codes, and your "Beach day" post has the id 81 in Instagram's posts table.

DELETE FROM posts WHERE id = 81;postsidusernamecaptionlikes79sam.codesGym day1280maya.devNew desk781sam.codesBeach day13

You tap Delete on it.

So Instagram's server might send a DELETE FROM posts, where the id is 81.

Only the row with id 81 passes, so only that post is removed.

So it's gone from your profile, and your other posts stay.

And what happens when you clear a chat on WhatsApp?

Say WhatsApp keeps messages in a table, one row for each message, with its chat's id.

DELETE FROM messages WHERE chat_id = 42;messagesidchat_idtext30142Hi Mom31142Where are you?…42…38057See you at 738163lol

Your chat with Mom has the id 42, and 57 messages.

You tap Clear chat.

So WhatsApp's server might send a DELETE FROM messages, where the chat id is 42.

All 57 rows pass, so Postgres removes all 57, and your other chats keep theirs.

So DELETE removes every row that passes its WHERE, whole rows at a time.

Example: Taking a row out for good

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres DELETE FROM users WHERE xp = 40;.

How many rows are removed?

Explanation

First, the WHERE keeps only rows whose xp is exactly 40.

Then, Ben's row passes, and so does Eli's, and no other row has 40.

Therefore, 2 rows are removed: Ben's and Eli's.

Question 1

Simplified's users table holds six learners: Ada, Ben, Cy, Dee, Eli and Fay, with one row each. Its server sends Postgres DELETE FROM users WHERE name = 'Fay';.

How many rows does users hold afterwards?

rows

Question 2

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres DELETE FROM users WHERE xp < 50;.

How many rows are removed?

rows

Question 3

Postgres has handed out ids 1 to 6 in Simplified's users table. Cy, whose id is 3, deletes the account, and Cy's row is removed. Then a new learner signs up, and the INSERT leaves out id.

What id does the new learner get?

Question 4

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres DELETE FROM users WHERE xp > 1000;.

How many rows are removed?

rows

Question 5

Simplified's server sends Postgres DELETE FROM users WHERE id = 2;.

What does it remove?

Question 6

Say Instagram keeps profiles in a table, one row for each account, with a bio column that may be NULL. You clear your bio, and your account's id is 205.

Which query empties just your bio?

What a missing WHERE does

DELETE FROM users WHERE id = 3;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee.codes@gmail.comDee K3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.org—402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30UPDATE users SET xp = xp + 10;no WHERE
UPDATE users SET xp = xp + 10;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.org—402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
Simplified's serverUPDATE users SET xp = xp + 10;Postgres
DELETE FROM users;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.org—402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30
UPDATE weekly_xp SET xp = 0;weekly_xpidlearnerxp1you852maya1203sam40
1SELECT * FROM usersWHERE id = 6;resultidemailnamexpcreated_at6fay@simplified.orgFay752026-04-112UPDATE usersSET xp = xp + 10WHERE id = 6;usersidemailnamexpcreated_at6fay@simplified.orgFay75 → 852026-04-11
UPDATE users SET xp = xp + 10;usersidemailnamexpcreated_at1ada@simplified.orgAda130 → 1402026-01-052ben@simplified.orgBen40 → 502026-01-094dee.codes@gmail.comDee K300 → 3102026-03-025eli@simplified.org—40 → 502026-03-206fay@simplified.orgFay75 → 852026-04-117gia@simplified.orgGia0 → 102026-11-05 09:30… all 2,000,000 rows

Databases · Lesson 2.3

Changing and Removing Rows

What a missing WHERE does

Simplified
0:00 / 2:27

So Cy's DELETE, where the id is 3, removed Cy's row, and only Cy's.

The WHERE is what kept the other rows: each one failed it.

Now say Fay, with id 6 and 75 XP, finishes a lesson worth 10 XP.

But say a developer's code leaves out the WHERE.

So the server sends an UPDATE that sets xp to xp plus 10, and stops there.

UPDATE users SET xp = xp + 10;no WHERE

So which rows does Postgres change?

UPDATE users SET xp = xp + 10;usersidemailnamexpcreated_at1ada@simplified.orgAda130 → 1402026-01-052ben@simplified.orgBen40 → 502026-01-094dee.codes@gmail.comDee K300 → 3102026-03-025eli@simplified.org—40 → 502026-03-206fay@simplified.orgFay75 → 852026-04-117gia@simplified.orgGia0 → 102026-11-05 09:30… all 2,000,000 rows

Postgres still goes through users one row at a time.

But with no WHERE, there's no condition for any row to fail.

So Fay's row changes: 75 becomes 85.

But so does Ada's, from 130 to 140, and Ben's, from 40 to 50.

And so does every other learner's, all the way to the last row.

With all 2,000,000 learners in users, every one would get 10 XP they never earned.

And does Postgres check first?

Simplified's serverUPDATE users SET xp = xp + 10;Postgres

No: it makes the change straight away.

Nothing asks "are you sure?", because the query says exactly what to do: every row.

So what does a DELETE with no WHERE do?

DELETE FROM users;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.org—402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30… all 2,000,000 rows

A DELETE FROM users with no WHERE has no condition either, so every row passes.

So Postgres would remove every row: with all 2,000,000 learners, all 2,000,000 rows.

users would still be there, with its schema, but it would hold no rows at all.

So counting the rows in users would give 0.

So is a missing WHERE always a mistake?

No: sometimes every row really should change.

Say Duolingo shows your XP for this week, and starts it again at 0 every Monday.

UPDATE weekly_xp SET xp = 0;weekly_xpidlearnerxp1you85 → 02maya120 → 03sam40 → 0

Say it keeps everyone's XP for the week in a table, one row for each learner.

So early on Monday, Duolingo's server might send an UPDATE that sets xp to 0, with no WHERE.

Every row passes, so every learner's weekly XP goes back to 0.

That's exactly what it's meant to do.

So how do you make sure a write hits only the rows you mean?

1SELECT * FROM usersWHERE id = 6;resultidemailnamexpcreated_at6fay@simplified.orgFay752026-04-112UPDATE usersSET xp = xp + 10WHERE id = 6;usersidemailnamexpcreated_at6fay@simplified.orgFay75 → 852026-04-11

A safe habit is to run a SELECT with the same WHERE first.

A SELECT for every column, where the id is 6, sends back one row: Fay's.

So an UPDATE with that same WHERE will change exactly that row.

If the SELECT had sent back every row, you'd know the WHERE was wrong, before anything changed.

So with no WHERE, UPDATE and DELETE hit every row in the table, without asking.

Example: What a missing WHERE does

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres UPDATE users SET xp = xp + 5;.

How many learners' XP changes?

Explanation

First, the query has no WHERE, so there's no condition for any row to fail.

Then, every row passes, from Ada's to Fay's, and each one's xp goes up by 5.

Therefore, all 6 learners' XP changes.

Question 1

Simplified's users table holds 2,000,000 rows. Its server sends Postgres UPDATE users SET xp = xp + 10;, with no WHERE.

How many rows change?

rows

Question 2

Simplified's users table holds 2,000,000 rows. Its server sends Postgres DELETE FROM users;.

What happens?

Question 3

A developer sends Postgres UPDATE users SET xp = 0; by mistake, meaning to reset one test learner.

What does Postgres do before it changes the rows?

Question 4

Simplified's users table holds 6 rows. Postgres gets DELETE FROM users;, with no WHERE. Then it gets one INSERT that adds one new row.

How many rows does users hold now?

rows

Question 5

A developer is about to send DELETE FROM users WHERE xp < 10; to Simplified's users table.

What safe habit makes sure it removes only the rows they mean?

Question 6

Say Gmail keeps every user's emails in one table, with an owner column holding the owner's address, and a read column. You tap Mark all as read in your inbox, as you@gmail.com.

Which query should Gmail's server send?

How many rows did that change?

Simplified's serverUPDATE … WHERE id = 1;?Postgres
command tag
names the write, and how many rows it touched
UPDATE users SET xp = xp + 10WHERE id = 1;usersidemailnamexpcreated_at1ada@simplified.orgAda120 → 1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30UPDATE 1
1DELETE FROM users WHERE id = 3;DELETE 12UPDATE users SET xp = xp + 10;UPDATE 20000003INSERT INTO users (email, name) VALUES (…);INSERT 0 14INSERT INTO playlist_songs VALUES (…), (…), (…);INSERT 0 3
UPDATE users SET xp = xp + 10WHERE id = 3;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.org—402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30UPDATE 0
UPDATE emails SET folder = 'Trash'WHERE id IN (5, 8, 9);emailsidsubjectfolder4Lunch?Inbox5Your order shippedInbox8Re: projectInbox9Photos from SundayInboxUPDATE 3
1DELETE FROM posts WHERE id = 81;DELETE 12DELETE FROM posts WHERE id = 81;DELETE 0
1DELETE FROM users WHERE id = 3;DELETE 12UPDATE users SET xp = xp + 10;UPDATE 20000003INSERT INTO users (email, name) VALUES (…);INSERT 0 14INSERT INTO playlist_songs VALUES (…), (…), (…);INSERT 0 3

Databases · Lesson 2.4

Changing and Removing Rows

How many rows did that change?

Simplified
0:00 / 2:31

So a forgotten WHERE would change every learner's row, and nothing would ask "are you sure?".

And Ada's UPDATE, where the id is 1, changed only Ada's row.

But Simplified's server never looks inside users itself.

It only sees what Postgres sends back.

Simplified's serverUPDATE … WHERE id = 1;?Postgres

So how does the server know how many rows a write changed?

After every INSERT, UPDATE and DELETE, Postgres sends back a short report.

It's called a command tag.

A command tag names the write, and how many rows it touched.

UPDATE users SET xp = xp + 10WHERE id = 1;usersidemailnamexpcreated_at1ada@simplified.orgAda120 → 1302026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30UPDATE 1

Ada's UPDATE changed one row.

So Postgres sent back UPDATE 1.

So what did the other writes send back?

1DELETE FROM users WHERE id = 3;DELETE 12UPDATE users SET xp = xp + 10;UPDATE 20000003INSERT INTO users (email, name) VALUES (…);INSERT 0 14INSERT INTO playlist_songs … VALUES (…), (…), (…);INSERT 0 3

Cy's DELETE removed one row, so it sent back DELETE 1.

The forgotten WHERE would change every row, so with 2,000,000 learners it would send back UPDATE 2000000.

And Gia's INSERT sent back INSERT 0 1.

An INSERT's tag has two numbers. The first is always 0 in Postgres.

The last one, 1, is how many rows it added.

So Spotify's INSERT for your three songs would send back INSERT 0 3.

But what if no row passes the WHERE?

UPDATE users SET xp = xp + 10WHERE id = 3;usersidemailnamexpcreated_at1ada@simplified.orgAda1302026-01-052ben@simplified.orgBen402026-01-094dee.codes@gmail.comDee K3002026-03-025eli@simplified.org—402026-03-206fay@simplified.orgFay752026-04-117gia@simplified.orgGia02026-11-05 09:30UPDATE 0

Say Cy deleted the account on a phone, while a lesson was still open on a laptop.

Then Cy finishes that lesson, and the server sends an UPDATE to add 10 XP, where the id is 3.

Postgres checks every row, and none has id 3 any more.

So no row changes, and the tag is UPDATE 0.

That's not an error: the query worked, and no row passed, like an empty result.

So the server checks the count, and 0 tells it Cy's row isn't there.

So how might Gmail know to say "3 conversations moved to Trash"?

Say Gmail keeps emails in a table, with a folder column.

UPDATE emails SET folder = 'Trash'WHERE id IN (5, 8, 9);emailsidsubjectfolder4Lunch?Inbox5Your order shippedInbox → Trash8Re: projectInbox → Trash9Photos from SundayInbox → TrashUPDATE 3

You select three emails, and tap Delete.

Gmail's server might send an UPDATE that sets their folder to Trash.

Three rows pass, so the tag is UPDATE 3.

So Gmail might take that 3, and show "3 conversations moved to Trash".

And what if you delete the same Instagram post twice?

Say you tap Delete on your post on your phone, and then on your laptop.

1DELETE FROM posts WHERE id = 81;DELETE 12DELETE FROM posts WHERE id = 81;DELETE 0

The first DELETE removes the row with id 81, and sends back DELETE 1.

The second DELETE finds no row with id 81, so it sends back DELETE 0.

So Instagram's server knows the post was already gone, and nothing went wrong.

So every write sends back how many rows it touched, and 0 means none passed.

Example: How many rows did that change?

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres UPDATE users SET xp = xp + 10 WHERE xp = 40;.

What command tag comes back?

Explanation

First, the WHERE keeps rows whose xp is exactly 40: Ben's and Eli's.

Then, Postgres changes those 2 rows, and reports how many it touched.

Therefore, the tag is UPDATE 2.

Question 1

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres UPDATE users SET xp = xp + 10 WHERE xp < 50;. The command tag that comes back is UPDATE and a number.

What is the number?

Question 2

Simplified's server sends Postgres an UPDATE, and the command tag that comes back is UPDATE 0.

What does it tell the server?

Question 3

Say WhatsApp keeps messages in a table, with each message's chat id. You clear your chat with Sam, id 57, on your phone, and its server sends DELETE FROM messages WHERE chat_id = 57;, which sends back DELETE 12. A minute later, you tap Clear chat on your laptop too, and the same DELETE sends back DELETE 0.

What happened the second time?

Question 4

Simplified's server sends Postgres an INSERT for a new learner, and the command tag INSERT 0 1 comes back.

What does it report?

Question 5

Ada finishes a lesson, and Simplified's server sends UPDATE users SET xp = xp + 10 WHERE id = 1;.

Why does the server check the number in the command tag that comes back?

Question 6

A table holds 6 rows. Its server sends Postgres a DELETE, and the command tag DELETE 2 comes back.

How many rows does the table hold now?

rows
Assistant

Highlight lesson text and click Ask AI, or type a question below.