Updating and deleting data

UPDATE changes values in existing rows: UPDATE table_name SET attribute1 = value1, attribute2 = value2, ... WHERE condition;

• UPDATE STUDENT SET GUID = 101010101010 WHERE RollNumber = 3; gives student 3 a guardian ID.
• Several columns can be changed in one statement by listing them after SET, separated by commas.
• Without WHERE, UPDATE changes every row in the table, which is rarely what you want.

DELETE removes rows: DELETE FROM table_name WHERE condition;

• DELETE FROM STUDENT WHERE RollNumber = 2; removes that student's record.
• Without WHERE, DELETE removes every row: the table stays, but empty. (DROP TABLE, by contrast, removes the table itself.)

INSERT, UPDATE and DELETE (with SELECT for reading) are DML statements: they work on the data, while DDL statements (CREATE, ALTER, DROP) work on the structure.

Good habit: before running an UPDATE or DELETE, run a SELECT with the same WHERE clause to see exactly which rows will be affected.

Example (new): UPDATE MEMBER SET Sport = 'Badminton' WHERE MemberID = 17; fills in the missing sport for one member; DELETE FROM MEMBER WHERE JoinDate < '2015-01-01'; removes members who joined before 2015, and nobody else.