Snowflake UPDATE: How to Update Rows in a Table
The UPDATE command in Snowflake is like giving your data a fresh coat of paint. It allows you to change existing values in your database table based on specific criteria. This can be especially useful when you need to correct or modify data after it’s been entered.
What it does
The UPDATE function changes the data in one or more columns of a table. You can specify exactly which rows should be updated using conditions (WHERE clause), and you can even pull in information from other tables to determine what the new values should be (FROM clause).
Syntax
Here’s how to structure an UPDATE statement that can optionally incorporate additional tables:
_10UPDATE <target_table>_10SET <column_name> = <new_value> [, <other_column> = <other_value> ...]_10[FROM <additional_table(s)>]_10[WHERE <condition>];
where:
<target_table>: The table you’re updating.<column_name>: The column(s) in the target table you want to update.<new_value>: The new value for the column(s).<additional_table(s)>: (Optional) Additional table(s) from which to pull data for complex updates.<condition>: (Optional) Conditions that specify which rows in the target table should be updated.
Examples
Updating table using data from another table
Let’s say we have two tables: character_info that stores character details, and new_locations that contains updated location information for some characters.
Input
Table: character_info
| character_id | name | location |
|---|---|---|
| 1 | Arthur Dent | Earth |
| 2 | Ford Prefect | Betelgeuse Five |
Table: new_locations
| character_id | new_location |
|---|---|
| 1 | Heart of Gold |
| 2 | The Restaurant at the End of the Universe |
Snowflake SQL Query
_10UPDATE character_info_10SET location = new_locations.new_location_10FROM new_locations_10WHERE character_info.character_id = new_locations.character_id;
Output
Table: character_info
| character_id | name | location |
|---|---|---|
| 1 | Arthur Dent | Heart of Gold |
| 2 | Ford Prefect | The Restaurant at the End of the Universe |
Explanation
- FROM Clause: This example uses the FROM clause to join the character_info table with the new_locations table.
- SET Clause: The location of each character in character_info is updated to their new location from the new_locations table based on matching character_id.
- WHERE Clause: Ensures that the update happens only where there is a corresponding match in the new_locations table.
