Locked lesson.

Upgrade

About this lesson

The UPDATE statement can be used to change data that already exists in the SQL database

Exercise files

Download this lesson’s related exercise files.

UPDATE Statement.docx
59 KB UPDATE Statement - Solution.docx
59.5 KB

Quick reference

UPDATE Statement

The Update Statement allows us to update records in our table with SQL.

When to use

Use Update whenever you want to make changes to the data in your table.

Instructions

UPDATE [dbo].[Current_Customers]
    SET [Last Name] = 'Smithertonly'
    WHERE [First Name] LIKE 'Tracy'

This will update the last name of any customers with the first name 'Tracy' to 'Smithertonly'. In our case we only have one customer named Tracy, so it will only update for her.

If you had many customers named Tracy, then you could use something else unique to them, such as their customer ID, in your Where Clause.

Hints & tips

  • Update allows us to change data in our table
  • Be sure to add a where clause or ALL your columns will be updated instead of just one!
Login to download
  • 00:04 In this video I wanna look at the update statement.
  • 00:07 So we've learnt how to add data into our table with the insert statement.
  • 00:12 Now, we need to learn how to update stuff, in case something changes over time.
  • 00:17 Somebody's email address changes, so we need to update it.
  • 00:20 Their home address changes, whatever.
  • 00:22 We need a way to do that programmatically.
  • 00:23 Of course, we can do that by hand by going, like we have in the past,
  • 00:27 right clicking on here, typing in edit, highlighting the thing and
  • 00:30 changing it by hand.
  • 00:31 But most of the time, you're not gonna wanna do that.
  • 00:33 You're gonna wanna do it programmatically.
  • 00:34 So to do that, we use the update statement.
  • 00:36 And it's pretty simple.
  • 00:37 There's only one little thing you have to sort of pay attention to and
  • 00:39 I'm gonna mention that in just a second.
  • 00:42 So let's get rid of this and we can leave that as supposed.
  • 00:47 So, this is our current table.
  • 00:49 So, we've got Tracy Smitherton, maybe I misspelled Smitherton.
  • 00:53 So, let's go ahead and update that.
  • 00:54 So an update statement is pretty simple it's just Update.
  • 00:58 That's one thing I like about SQL, everything is just simple.
  • 01:01 You wanna update, you type update.
  • 01:02 You wanna select, you type select.
  • 01:04 You wanna insert, you type insert.
  • 01:06 What could be simpler?
  • 01:07 So the table name, so we dat and that.
  • 01:11 And it's just dbo current customers.
  • 01:15 And then, the next line is set and this is what you want to update.
  • 01:19 We want to update last name and
  • 01:22 we want to set it equal to lets say Smithertonly.
  • 01:28 Okay, now here is the tricky part you have to type where, and
  • 01:32 then give it a condition.
  • 01:33 And if you don't do this, everything will get updated and you don't want that.
  • 01:37 So you have to tell it exactly what to update.
  • 01:39 We're saying update the last name column to Smithertonly.
  • 01:43 If we don't tell it which specific record to update, it will update all of them, and
  • 01:47 we don't want that.
  • 01:48 So normally, when we design our database, we would give each of these like an ID
  • 01:52 that was unique and we could use that in order to do this.
  • 01:56 We did not do that so lets say where first name equals Tracy.
  • 02:02 Change this to like, where the first name is like Tracy and
  • 02:06 use the equal to with text, of course.
  • 02:08 Okay so, now, we've executed it and it's Smithertonly and
  • 02:13 none of the other records have changed.
  • 02:16 So, pretty simple update, name your table,
  • 02:20 set the thing equal to what you want to change.
  • 02:23 Let's change this back to Smitherton.
  • 02:26 We execute it again, boom Smitherton.
  • 02:29 Don't forget your where statement right here.
  • 02:33 Or else like I said, all of these columns will get changed.
  • 02:36 And we don't want that.
  • 02:37 So that's the update statement.
  • 02:39 In the next video, we'll look at the delete statement.

Lesson notes are only available for subscribers.

Gift this course