SQL UPDATE CommandUpdate command in SQL is used to change any record in the table. Records are to be manipulated or updated using update command. Conditional update is the most common type of update command used in MySQL also. You are requested to go through the SQL WHERE command before using update command as both are to be used in proper combinations. Here is one simple command but before trying this please understand the implication of this. Without using any where command (or without using any restriction) the command will change all the records of the table. So let us start with the simple command.
This command will change all the records of the table student and will change all the class field to Five. This is not what is required in common cases so we will be changing records based on some conditions. Now we will change all the class four students to class five. Our command should selectively update those records for which class is equal to “four” and will update them to “five”. We will use one where clause along with update command for updating the records.
This will only change the records for which class=four and mark is more than or equal to 70. We have added two conditions by using AND as a logical operator. This way we can continue with adding more AND combinations to the query. Depending on the logic requirement OR combination can be added to the WHERE clause to UPDATE the records.
Updating multiple columns of a tableWe will use one more student table where we will store mark obtained by students in three subjects. You can download sql dump of this table at the end of this tutorial.
Update mark of the studentLet us update the new marks for Krish Star ( id =4)
Let us increase the mark of Arnold ( id =3 ) by 5 in all subjects
By adding a Where condition we are restricting updating to one record only. Without using Where clause we can update the total table with new data. Let us try to update student marks to 0.
Updating second table with data from first tableThe second table columns gets updated by taking data from first table. Let us say we have one table where students test marks are stored along with other details in other columns. We can collect only the test data and keep them in a separate table. Both the tables will have student id field which we will be using to link both tables.
Student mark table has two columns, s_id stores the student id and mark column stores total mark of the student. Let us first change the total mark of all students to zero.
Now let us update this mark column of student3_total table with sum of subject marks of student3 table. Here is the query.
Using Left Join
Update same table with average valueWe will create a table with student marks in three subjects. The average mark of each student in three subjects will be stored in a new column. In another column we will store the average mark of each class for all the students.
You can download the MySQL dump of the table with sample data here.
Adding average mark of each student.
We used LEFT JOIN to join the same table and get the average mark of each student. Now we will store the class average against each student by using GROUP BY Query.
Update with LEFT join using more than two tables
Updating a password field using md5 encryptionWe can update the password field with md5 encryption. To store this data we will change the length of field to 32 char. Here is a command to update password field with new md5 encrypted password.
Substituting part of a data of a field using replace command in your SQL statement
Download sql dump of student3 table
Download sql dump of student3_total
Download sql dump of student3_avg
This article is written by plus2net.com team.
More on Alter or coy table , add update or delete records in SQL