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.