Updating Your Data

Episode #6 of the course Intro to SQL by Mike Schmitt

 

There are instances when one or more fields need to be updated on a record. For instance, what if a user’s mailing address or email changes and needs to be updated? Or if a value was entered incorrectly and needs to be corrected? We can use an UPDATE statement to modify the values of records in a table. Here is the current state of our Users table:

Let’s update the email address to ‘the_house@springfield.gov’ for the user in our last example where the UserID was 5. Here is the SQL:

UPDATE Users SET Email='the_house@springfield.gov' WHERE UserID=5;

Our command is UPDATE, which is running against the Users table, we are setting the value of the Email field to ‘the_house@springfield.gov,’ and we are only doing this for the record with a UserID of 5. We do not want to update all the records, so be sure to include the WHERE clause! Once the statement is run, the table will look like this:

There could be instances where you want to update every record on a table, and in those cases, you would not need a WHERE clause. If we wanted to remove the Gender information by nulling the field, which is essentially blanking out the field, we could run the following SQL statement:

UPDATE Users SET Gender=NULL;

This would run against every record on the Users table and result in the dataset showing like so, where the Gender information is blank:

And now the data is gone for every record!

And that’s how you can modify the data of existing records!

Explore on your own with an SQL Fiddle of today’s lesson! Since this is not a SELECT statement, your syntax must be written in the panel on the left-hand side.

 

Recommended book:

“Learning SQL” by Alan Beaulieu

 

Share with friends