Databases · Lesson 2.1
Changing a value in a row
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.
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.
So what does Postgres do with this UPDATE?
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?
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?
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.
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.
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.
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.
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.
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?
Databases · Lesson 2.2
Taking a row out for good
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.
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.
So what does Postgres do with it?
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?
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.
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.
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.
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.
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.
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.
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?
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?
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?
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?
Simplified's server sends Postgres DELETE FROM users WHERE id = 2;.
What does it remove?
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?
Databases · Lesson 2.3
What a missing WHERE does
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.
So which rows does Postgres change?
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?
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?
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.
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?
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.
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.
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?
Simplified's users table holds 2,000,000 rows. Its server sends Postgres DELETE FROM users;.
What happens?
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?
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?
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?
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?
Databases · Lesson 2.4
How many rows did that change?
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.
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.
Ada's UPDATE changed one row.
So Postgres sent back UPDATE 1.
So what did the other writes send back?
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?
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.
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.
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.
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.
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?
Simplified's server sends Postgres an UPDATE, and the command tag that comes back is UPDATE 0.
What does it tell the server?
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?
Simplified's server sends Postgres an INSERT for a new learner, and the command tag INSERT 0 1 comes back.
What does it report?
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?
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?