Now that we've learned how to add new information into our database, the next step is learning how to change what's already there. Updates in SQL server are not difficult. However, the power of the update statement is great, as we'll see in an example during this lesson. If you don't know what you're doing, you can cause some serious damage to your database!

The Basic Update Statement

The update statement has various valid syntaxes. That means there are multiple ways to write a valid update statement. We're going to focus on the style that will serve you best in the long run. Later on, you'll be writing updates that also have JOIN statements and other complications. Using this syntax from the start will make it easier to transition into doing more complex statements later on.

Syntax:
UPDATE [table]
SET [column] = [value]
FROM [table]
Example:
UPDATE Students
SET Address = 'Unknown'
FROM Students

When we run the statement we'll get the following back from the SQL server:

4 row(s) updated.

Now take a look at our table:

SELECT *
FROM Students
update

Did we really mean to update every single row? Maybe. This is why the update statement is dangerous. You can accidentally modify an entire table worth of data if you neglect to include a WHERE clause. Let's look at a safer example that only updates one student's grade point average.

UPDATE Students
SET GPA = 4.0
FROM Students
WHERE StudentID = 3
When we run this statement, we get back the following:
1 row(s) updated.

Now, lets take another look at our table and we see a much more focused update took place.

update

This time, only the field we specified for the row we specified got updated. This is why it's important never to forget that where clause! Now you know the basics of updating a single field with a SQL UPDATE statement. However, there is still a lot to learn!

Updating Multiple Fields

We'll now look at how to update more than one field at a time. Let's say Jon Doe changed his name to John Dough. Since first and last name are stored separately, we need to update two fields. Much like the select statement, we can include a list of fields in the SET statement.

UPDATE Students
SET FirstName = 'John', LastName = 'Doug' 
WHERE StudentID = 1

Although we're still only updating a single row, we can change more than one field at a time. This becomes useful when writing SQL that sends all the changes made on a webpage form to update the related row in the database. We'll talk more about this later on.

Updates With Joins

Let's say we've been asked to put a N/A in the GPA filed for any student taking a two-credit class. This is a complex problem. The GPA field is on the Student table, but finding out who has a two-credit class involves at least two other tables. How do we work this out?

Often these types of updates seem overwhelming to beginners. Where do you start? If we begin with writing a select, we'll have a much easier time identify what data we're looking for. Since updates are dangerous when done wrongly, this also gives us a chance to preview the data that we'll be updating.

Let's start by creating the SELECT to show this data. We'll need to start with the Students table since that's the one we're going to update and then add the related tables that get us over to class credits. To find out which students are in which classes, we'll need to look at the StudentClasses intersecting table. Finally, from there, we can join to the Class table which tells us how many credits classes are worth.

SELECT FirstName, LastName, GPA, Credits 
FROM Students
INNER JOIN StudentClasses
ON Students.StudentID = StudentClasses.StudentID
INNER JOIN Classes
On StudentClasses.ClassID = Classes.ClassID
WHERE Credits = 2

Let's run this and see what comes back.

update

Now we have a query that has all the information that would be involved in our update statement. We can see the GPA, which is what we'll be changing, and the Credits, which provide our criteria for what should or should not change. Now we can use this to formulate our update statement.

--SELECT FirstName, LastName, GPA, Credits

UPDATE Students
SET GPA = 'N/A'
FROM Students
INNER JOIN StudentClasses
ON Students.StudentID = StudentClasses.StudentID
INNER JOIN Classes
On StudentClasses.ClassID = Classes.ClassID
WHERE Credits = 2

As you can see, we only had to change the top part of the statement. The rest of it stays the same! This way we can be confident that this UPDATE statement will not update the wrong data.

Recap

Don't forget these two important tips when running an update statement:

  • Always Include a WHERE clause unless you mean to update everything.
  • Build a SELECT that brings back the data you wish to update before running an UPDATE statement.

The next command we'll learn is probably obvious. We've learned how to insert data and also update it. At some point, we'll want to know how to erase data as well. Before we get to that, though, we need to learn about subqueries.



author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories