Update gives you a way to modify individual attributes of a single tuple, a group of tuples, or a whole table (or view).
Format:
UPDATE table/view SET col-name = {value | expression} [col-name = value | subquery,…] [WHERE update_criteria];
You can only update tuples already present in the table (i.e., you cannot use UPDATE to add new tuples). You can either UPDATE one table at a time. You don’t have to know the present value of a field to set it (although you can refer to it in the “expression” clause). The expression cannot be a sub-query or involve aggregate operations.
Example 1
Change the major of student ‘S1020’ to music. (Update a single field of one tuple)
UPDATE STUDENT SET MAJOR = ‘Music’ WHERE STUID = ‘S1020’;
Example 2
Change Tanaka’s department to MIS and rank to Assistant. (Update several fields in one tuple)
UPDATE FACULTY SET DEPT = ‘MIS’ RANK = ‘Assistant’ WHERE FACNAME = ‘Tanaka’;
Example 3
Change the major of student ‘S1013’ from math to NULL. (Updating using NULL)
UPDATE STUDENT SET MAJOR = NULL WHERE STUID = ‘S1013’;
Example 4
Change grades of all students in ‘CIS201A’ to A. (Updating several tuples)
UPDATE ENROLL SET GRADE = ‘A’ WHERE COURSENUM = ‘CIS201A’;
Example 5
Give all students three extra credits. (Update all tuples)
UPDATE STUDENT SET CREDITS = CREDITS + 3;
Example 6
Change the room to ‘B220’ for all courses taught by Tanaka. (Updating with a subquery)
UPDATE CLASS SET ROOM = ‘B220’ WHERE FACID = (SELECT FACID FROM FACULTY WHERE FACNAME = ‘Tanaka’);