Modify data in a table

- [Instructor] Data in the database can be modified using the UPDATE keyword. Update is useful where we want to change information that's already there, rather than adding new records with different information. To update information, we have to tell the database which table we're using, what fields to update with what values, and optionally, use a WHERE clause to specify how to find records to update. If you leave off a WHERE clause, the change will apply to the whole table, so be careful with that. Let's change the name for a recent entry from George to Martha. I know there's a value that we just added for George White, but we can double check that with SELECT * FROM people.

And scrolling down the bottom of the result, here's George White. I'll write an update statement to change that. I'll write UPDATE people, SET first_name='Martha', WHERE first_name='George' AND last_name='White'.

Again, this WHERE clause is important, because if I left it off, I'd set the first name of Martha for every record in the table. That's not what I want to do, and, in databases, there's often no undo. I want to be as specific as possible here. There are other George's in my database, and I don't want to change them. So I'm specifying both the first name, and the last name. I'll run this, and then let's take a look at the table again, with SELECT * FROM people.

I'll select just that statement, and run it. And here, now I have Martha White, instead of George White. Great. Because we have this WHERE clause that we can use to make updates, we can make changes to more than one record at a time, if we want to. Let's say one of the companies that our people work for got bought out, and its name changed. Let's say that Fisher LLC is now Megacorp Incorporated. Let's find records where the company is Fisher LLC.

I'll write SELECT * FROM people, WHERE company='Fisher LLC', and then to make a change to that, setting the company name to Megacorp, where the company is currently Fisher, I'll write UPDATE people, SET company='Megacorp Inc' WHERE company='Fisher LLC'.

I'll select this statement, and run it. My query executed successfully. And now, if we look for the people who work for Fisher LLC, there's no results. But if we change our search to Megacorp Incorporated, there they are. Take some time to explore updating data in this table.