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.

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’);

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>