UPDATE

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.

1. 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';

2. 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';

3. Example 3

Change the major of student 'S1013' from math to NULL. (Updating using NULL) UPDATE STUDENT SET MAJOR = NULL WHERE STUID = 'S1013';

4. Example 4

Change grades of all students in 'CIS201A' to A. (Updating several tuples) UPDATE ENROLL SET GRADE = 'A' WHERE COURSENUM = 'CIS201A';

5. Example 5

Give all students three extra credits. (Update all tuples) UPDATE STUDENT SET CREDITS = CREDITS + 3;

6. 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');

Basis Data