Learn online DBMS “Update Command in MySQL” Complete Lesson 12

UPDATE COMMAND– This command is used to update the attribute values of one or more tuples in a table. For example in the Teacher table, we want to update the Dept_No of teachers teaching computer science to 123. This can be done using the following command:

UPDATE Teacher

SET Dept_No=123

WHERE Subject=”Computer Science”;

We can also use the SELECT command in the WHERE clause of UPDATE command if we have to update the table based on result of a query. For example:

UPDATE Teacher

SET Subject=”Business Management”

WHERE Dept_No IN ( SELECTDept_ID

                                FROM Department

               WHERE Dept_Name=”Commerce”);

Thus the above command would set the subject of all the teachers that belong to the “Commerce” department to “Business Management”.

We can also have an algebraic expression in the SET clause. Suppose it is required to reduce the theTeacher_ID of a Teacher “Sanya” by 1, the command would be:

UPDATE Teacher

SET Teacher_ID=Teacher_ID-1

WHERETeacher_Name=”Shanaya”;