Lesson 27

SQL Update Table Set Value Where Condition

Learn how to use Structured Query Language (SQL) to update database records.

In particular, you learn how to use the UPDATE statement to set a particular column value for the rows in the database.

For most cases, you usually specify a WHERE clause to narrow down to a specific row. Otherwise, you would end up changing many, if not all, rows in the table by accident.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

Summary

Summary of Updating Records in SQL

In this discussion on updating records in SQL, the speaker outlines key principles and cautions related to the UPDATE command.

Key Points

Basic UPDATE Syntax:
- Use the following structure to update records:
```
UPDATE table_name SET column_name = new_value;
```
- For example, setting all customers' country to Canada:
```
UPDATE customers SET country = 'Canada';
```
Caution with UPDATE:
- If no WHERE clause is provided, the update will apply to all rows in the table.
- To prevent unintended changes, always specify a filter using a WHERE clause.
- Example of updating a specific record:
```
UPDATE customers SET country = 'Canada' WHERE customer_id = 2;
```
Impacts of Missing WHERE Clause:
- Demonstration of what happens when an update is executed without a WHERE clause, resulting in all records being changed.
- Importance of database resets and backups was emphasized, as real-world databases do not allow for easy recovery.
Database Practices:
- Regular backups are recommended to revert to a point in time if important data is accidentally modified.
- Use of transactions in programming can help manage updates safely, allowing for rollback in case of errors.
Exploring Important Data:
- Users can create data dumps to back up or explore data. This is typically done through command-line operations.

Conclusion

Always exercise caution when updating records in SQL to avoid unintentional changes and ensure regular backups are in place for data recovery.

Video Transcript

So what we're going to talk about next is how to update records. Okay, so to do that we're going to use update. We would update something. So you say a table name and then set and then whatever you want to update. For example country equals Canada. Now you got to be careful here too. If I say update the table name customer set country to Canada that means every single row will be set to country Canada. So it's the same thing delete. Make sure to add a where there to be specific about which row you want to change. So let's say for some reason Robert Luna won't move to Canada. So customer ID is two is that's Robert Luna. You remember here and if I do that he's now in Canada. Okay. So make sure to add a where there otherwise I'm going to update everything. And that's pretty much how you update anything. Just after the set you specify what columns you want to update and then here some narrow down some filtering to make sure you don't change everybody's rows by accident. Okay. You want to try what happens when I see without a where? Quick run. Everybody's now from Canada. Let's see what happened. So that's really bad. So I'm going to refresh so I get a reset everything so their countries are reset. Obviously in the real world you don't get this reset like I just did. So be careful. Is there a none to feature or do you have to read you the entire database? Yeah. So you have to be careful about database. Now usually people keep backups. That is every few moments in time. It backs up the database to a certain point in time. So you could technically try to bring it back to a certain point in time. You know how people might do really bad things in database. So that's how you recover. Now there's an undue feature. I don't know. There might be a way to like I know database you can log everything you do. So there might be a way of kind of going back to a previous snapshot. Now if you're working programming languages typically what you do is what's called a transaction. You try to do something but if anything fails it will just revert back. What's called a rollback. But that's just on the application like the client side. Not the database itself. Somebody were to manually go into the database and try to do stuff that's another network. Can we explore important data in SQL? Absolutely. You can always make what's called a dump of the data and there are different kinds of dumps. Usually you can do that with a command in the command line. Any of your database servers you can do that.