You'll often need to do more than just insert and select data from your database. In this lesson, we'll discuss how we can make changes to rows in our database that already exist.

Let's start off with setting up our database- with the table and some users already added:

CREATE TABLE Users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO Users (name, email, age) VALUES
("Oscar","oscar@email.com", 25),
("Mario","mario@email.com", 30),
("Andrea","andrea@email.com", 40);

Testing this in our DB-Fiddle- we can see the three users were successfully added and we can query for them with a statement.

Now what if one of our users goes into their profile page and updates their name? We'll need to send this update to our database- and for that we'll use the statement. Let's imagine our user "Oscar" wants to change his name to "Ozzy". Let's build out the statement together:

UPDATE

A basic statement needs 3 things.

• The Table we want to update
• The column we want to change
• The value we want to assign to the column

UPDATE Users
SET name = "Ozzy"

Let's run this in our DB fiddle and see what we get!

Uh oh! It worked, but we did too much. We've updated every single user to have their name change to "Ozzy".

That's not what we wanted.

Where

Just like with our SELECT statements, we can use "WHERE" to filter down to which specific row we want to update!

UPDATE Users
SET name = "Ozzy"
WHERE name = "Oscar";

0 Comments

Top comments

"Please login to view comments"

Join the Conversation!

Subscribing gives you access to the comments so you can share your ideas, ask questions, and connect with others.

Upgrade your account