SQL UPDATE and DELETE

SQL UPDATE and DELETE

Once data is in a database it often needs to be changed or removed. UPDATE modifies existing records; DELETE removes them. Both commands use a WHERE clause to target specific rows - without WHERE, every row in the table is affected, which is almost never the intention.

Warning: forgetting the WHERE clause on UPDATE or DELETE applies the change to every record in the table. Always double-check the condition before running these commands on real data.

The Table Used in These Examples

StudentIDSurnameFirstNameYearGroupGrade
1001KhanAisha10A
1002LeeJames11B
1003PatelPriya10A
1004SmithTom11C

UPDATE and DELETE Examples

UPDATE

UPDATE modifies the values in one or more columns for all rows matching the WHERE condition. The SET clause specifies which column to change and to what value. Multiple columns can be updated at once by separating them with commas in the SET clause.

UPDATE Students
SET Grade = 'B'
WHERE StudentID = 1004;
BEFORE ID Surname YrGrp Grade 1001Khan10A 1002Lee11B 1003Patel10A 1004Smith11C UPDATE Grade='B' AFTER ID Surname YrGrp Grade 1001Khan10A 1002Lee11B 1003Patel10A 1004Smith11B Smith's grade changed from C to B. All other rows unchanged.
Updating multiple columns at once
UPDATE Students
SET Grade = 'A', YearGroup = 11
WHERE StudentID = 1002;

Multiple columns are separated by commas in the SET clause. Both Grade and YearGroup are updated in the same statement for student 1002.

DELETE FROM

DELETE FROM removes entire records from a table. The WHERE clause identifies which rows to remove. Deleting a record is permanent - the row cannot be recovered unless a backup exists.

DELETE FROM Students
WHERE StudentID = 1004;
BEFORE IDSurnameYrGrpGrade 1001Khan10A 1002Lee11B 1003Patel10A 1004Smith11C DELETE ID=1004 AFTER IDSurnameYrGrpGrade 1001Khan10A 1002Lee11B 1003Patel10A Smith's row removed. Three rows remain.
Deleting multiple rows
DELETE FROM Students
WHERE YearGroup = 11;

This deletes every student in Year 11 (Lee and Smith) in a single statement. The WHERE condition determines how many rows are affected.

 Key Takeaways

  • UPDATE modifies values in existing records. SET specifies what to change; WHERE identifies which rows.
  • DELETE FROM permanently removes matching rows. WHERE identifies which rows to delete.
  • Without a WHERE clause, UPDATE and DELETE affect every row in the table - this is almost always a mistake.
  • Multiple columns can be updated in one statement by separating SET assignments with commas.