SQL UPDATE
UPDATE changes data that is already in a table.
UPDATE Syntax
Example
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;SETsays which columns get which new valuesWHEREsays which rows to change
Update One Row
Hamza Raza has now chosen the Digital Marketing course (id 4):
Example
UPDATE students
SET course_id = 4
WHERE id = 5;1 row updated.
Example
SELECT name, course_id FROM students WHERE id = 5;| name | course_id |
|---|---|
| Hamza Raza | 4 |
Using the id in WHERE is the safest way to change exactly one row, because each id is unique.
Update Several Columns
Separate the columns with commas. Bilal moved to Lahore and his marks were corrected after a recheck:
Example
UPDATE students
SET city = 'Lahore', marks = 68
WHERE id = 3;| id | name | city | marks |
|---|---|---|---|
| 3 | Bilal Ahmed | Lahore | 68 |
Update Many Rows
A WHERE condition can match many rows. The institute decides that every 3-month course will now take 4 months:
Example
UPDATE courses
SET duration_months = 4
WHERE duration_months = 3;2 rows updated.
| id | title | duration_months |
|---|---|---|
| 1 | Web Development | 6 |
| 2 | Python Programming | 4 |
| 3 | Graphic Designing | 4 |
| 4 | Digital Marketing | 4 |
Use the Old Value
The new value can be calculated from the current value. Here every student from Karachi gets 2 grace marks:
Example
UPDATE students
SET marks = marks + 2
WHERE city = 'Karachi';Each matching row uses its own current marks, so Ali goes from 78 to 80 and Hamza from 70 to 72.
Warning: Do Not Forget WHERE
If you leave out WHERE, every row in the table is changed:
Example
UPDATE students
SET city = 'Karachi';6 rows updated.
Now all students live in Karachi, and the real cities are lost.
Before running an UPDATE, first run a SELECT with the same WHERE clause. If it shows exactly the rows you expect, change SELECT ... FROM to UPDATE ... SET. Many tools also let you work inside a transaction, so a mistake can be undone with ROLLBACK.
Quick Reference
| Goal | Statement |
|---|---|
| Change one row | UPDATE students SET age = 20 WHERE id = 1; |
| Change two columns | UPDATE students SET age = 20, marks = 80 WHERE id = 1; |
| Change matching rows | UPDATE students SET marks = 0 WHERE marks IS NULL; |
| Change all rows | UPDATE students SET marks = 0; (careful!) |
Exercise
Exercise
What happens when you run: UPDATE courses SET duration_months = 5;