Lesson 7 of 15 · 20 min
Changing rows with UPDATE
By the end of this lesson you can write an UPDATE that changes exactly the rows you intend, preview it with a SELECT using the same WHERE, and update one table using values from another.
UPDATE changes values in rows that already exist. It has three parts:
UPDATE tablenames the table to change.SET column = expressionsays what the new value is. The expression can use the row's current values:price = price * 1.10reads the old price and writes the new one.WHERE conditionchooses which rows change. It works exactly like the WHERE you know from SELECT.
PostgreSQL goes through the table, tests every row against the WHERE condition, and rewrites only the rows where it is true. Without a WHERE clause, every row in the table is changed. There is no confirmation dialog and no undo button (outside a transaction, which you'll meet in lesson 13). When it finishes, PostgreSQL reports how many rows it changed, for example UPDATE 2. Read that number every time: it is your first check that the statement did what you meant.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons