SQL ET POSTGRE 4.18 : Editing data in a table

In this video we learn how to modify data in a table. We continue with the exemple table we populated in the previous lesson. The SQL syntax is: UPDATE followed by the table name, SET column = newValue for the new value, and a WHERE clause to filter which rows to update. String values go between single (or double) quotes, integers do not.

Update one row by id

Let's change Mike's nationality from GB to US. We write UPDATE exemple SET nationalite = 'US' WHERE exemple_id = 2; — filtering by the primary key is the safest way to target exactly one row. We run it, then verify with SELECT * FROM exemple: Mike is now US and his row may appear last in the listing, but his id has not changed. 
UPDATE exemple SET nationalite = 'CA' WHERE nationalite = 'US';
UPDATE exemple SET prenom = 'Anke', age = 37 WHERE exemple_id = 4;
  • Always include WHERE; without it, the change is applied to every row.
  • You can update several rows at once by filtering on a shared column.
  • You can change several columns at once, comma-separated in the SET clause.
We can also update several rows in a single statement without using the primary key: changing Mike and John's nationality from US to CA with WHERE nationalite = 'US'. And we can update several columns at once: fixing Anke's first name to Anke and her age to 37 with SET prenom = 'Anke', age = 37 WHERE exemple_id = 4;. Always start with UPDATE, then SET for the new values, and WHERE to filter. In the next video we will see how to delete data from a table.

Summary

This lesson demonstrates how to modify existing data in PostgreSQL tables using the UPDATE statement. It covers the essential syntax (UPDATE table SET column = value WHERE condition) with three practical examples: updating a single column for one row using the primary key, changing multiple rows matching a specific criterion, and modifying multiple columns in a single row. The WHERE clause is emphasized as critical for filtering which rows to update, with the primary key highlighted as the most reliable identifier for targeted modifications.

Key points

  • UPDATE syntax: Use UPDATE table_name SET column = new_value WHERE condition to modify existing data
  • WHERE clause is essential to filter and target specific rows; without it, the operation affects all rows
  • Use primary key (ID) as the most reliable condition to identify and update individual records
  • Multiple columns can be updated in one statement by separating assignments with commas
  • Multiple rows can be updated simultaneously by using non-unique column values in the WHERE clause
  • Always verify changes with a SELECT statement to confirm the UPDATE executed correctly

FAQ

What is the basic syntax for updating data in a PostgreSQL table?

The UPDATE statement follows this pattern: UPDATE table_name SET column_name = new_value WHERE condition. For example, UPDATE example SET nationality = 'US' WHERE id = 2 updates the nationality to 'US' for the row where id equals 2.

Why is the WHERE clause critical when using UPDATE?

The WHERE clause filters which rows the UPDATE applies to. Without it, the UPDATE would modify every row in the table. It ensures you only change the intended data, making it essential for data integrity.

Can you update multiple columns at the same time?

Yes, you can update multiple columns in one UPDATE statement by separating each column assignment with a comma. For example: UPDATE example SET firstname = 'Anne', age = 37 WHERE id = 4 updates both the firstname and age columns for the specified row.