SQL Fundamentals
Update Data in a Table with SQL Update
One of the core commands in SQL is update. This is how we can modify the data that is already in our database table. Let’s update our users table and change one of our user's duplicated user handle to a new UUID. We'll even use a postgres extension to generate new UUIDs.
UUIDs / GUIDs
Postgres - http://www.postgresqltutorial.com/postgresql-uuid/
SQL Server- https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-2017
MySQL - https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid
Oracle - https://docs.oracle.com/javase/8/docs/api/java/util/UUID.html
Instructor: [00:00] Our users' table currently has two rows of data, a Tyler Clark and a Debbie Jones that currently share the same user handle and create date.
[00:10] We want our user handles to be unique, which is why when we created this table, we gave this column a UUID type. Let's update my user handle to be a different value. Postgres has a handy extension we can bring in that will automatically generate a random UUID for us. I'll post links in the description on how to work with UUIDs in other databases.
[00:31] Now with this extension created, we can update our users' table with a new user handle. When working with the update command, there are three pieces of data needed. First is the name of the table and column we're updating. Here, we provide the table name and column name separated by this set word.
[00:49] Second is the new value that we want to update the row with. We'll do that by using the equal sign. Finally, which row to update? You might have noticed that we actually ended up updating both rows of data when we just wanted to update my user handle.
[01:04] While it did accomplish our goal of having unique user handles, if we're not careful, we could have shot ourselves in the foot by accidentally losing critical data. When working with SQL, commands like select, update, delete, these are looping commands, meaning unless a condition is passed it will loop over each row of data.
[01:23] Ideally, what we should have done is add a where clause on our command that only updated the user handle for the row where the last name is Clark. We'll get more into filtering and the where clause in another video.
[01:36] For now, all you needed to understand is that when updating, unless a condition is provided like we did the last name = Clark, it will apply to every single row in our table. Finally, we don't have to update one column at a time.
[01:49] We can update more than one column by simply comma separating them. By adding first name = Danny, I'm updating both my user handle and my first name.