Snowflake
Update Rows

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:


_10
UPDATE <target_table>
_10
SET <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_idnamelocation
1Arthur DentEarth
2Ford PrefectBetelgeuse Five

Table: new_locations

character_idnew_location
1Heart of Gold
2The Restaurant at the End of the Universe

Snowflake SQL Query


_10
UPDATE character_info
_10
SET location = new_locations.new_location
_10
FROM new_locations
_10
WHERE character_info.character_id = new_locations.character_id;

Output

Table: character_info

character_idnamelocation
1Arthur DentHeart of Gold
2Ford PrefectThe 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.
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free