Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson
1×
Become a member
to unlock all features

Level Up!

Access all courses & lessons on egghead today and lock-in your price for life.

Autoplay

    Update Data in a Table with SQL Update

    postgresPostgreSQL

    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

    Code

    Code

    Become a Member to view code

    You must be a Pro Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    orLog In
    Discuss

    Discuss

    Transcript

    Transcript

    Instructor: 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.

    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.

    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.

    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.

    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.

    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.

    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.

    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.