SQL UPDATE Command

MySQL Update Update command in SQL is used to change any record in the table. Records are to be changed using update command.
Conditional update is the most common type of update command used in MySQL. You can read 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 you should 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.
SQL UPDATE query to update columns with WHERE & AND conditions & with multiple columns data

Delete the student table if required by using this query.
DROP TABLE student
You can restore the table with data by using the SQL dump again.

Update all records by changing class to Five.
UPDATE student SET class='Five'
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.
UPDATE student SET class='Five' WHERE class='Four'
This command will update only those records for which class is equal to 'Four'. So this way we can update records selectively. Now let us move one more step and change the records selectively based on some value in some other field. We will change records for which class is equal to 'Four' and mark is more than 70. We will promote those students only who has got more than or equal to 70 mark.
You may have to restore the original table by using the SQL dump of student table given above.
UPDATE student SET class='Five' WHERE class='Four' and mark >= 70
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 table

We 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.
idnameclasssocialsciencemath
2Max RuinThree855685
3ArnoldThree554075
4Krish StarFour605070
5John MikeFour608090
6Alex JohnFour559080
7My John RobFifth786070
8AsruidFive858090
9Tes QrySix786070
10Big JohnFour554055
Download the SQL dump of this table at the end of this page.

Update mark of the student

Let us update the new marks for Krish Star ( id =4)
UPDATE student3 SET math=50, social=60, science=55 WHERE  id=4
Let us increase the mark of Arnold ( id =3 ) by 5 in all subjects
UPDATE student3 SET math=math+5, social=social+5, science=science+5 WHERE  id=3
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.
 UPDATE student3 SET math=0, social=0, science=0

To Update multiple records use INSERT ... ON DUPLICATE KEY UPDATE

Updating second table with data from first table


SQL UPDATE query to update columns in multiple tables using INNER & LEFT JOIN with GROUP BY query

The 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.
UPDATE student3_total SET mark=0
Now let us update this mark column of student3_total table with sum of subject marks of student3 table. Here is the query.
update  student3_total,student3  SET mark= (math+social+science)
  WHERE s_id=id
Using Left Join
UPDATE student3_total LEFT  JOIN student3 ON s_id=id 
SET   mark= (math+social+science)

Read more on LEFT JOIN

Using Inner Join
UPDATE student3_total INNER JOIN student3 ON s_id=id SET  
 mark= (math+social+science)

Update same table with average value

We 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.
update student3_avg a 
LEFT JOIN (select  id, sum(social + math + science )/3 as number 
FROM student3_avg group by id)
 as b on a.id=b.id set a.average=b.number
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 student3_avg a LEFT JOIN 
(select  class, avg(average) as number from 
student3_avg group by class)
 as b on a.class=b.class set a.average_class=b.number

Update with LEFT join using more than two tables

UPDATE  table1 a 
LEFT JOIN table2 b on a.ORD_NO=b.ORD_NO 
LEFT JOIN table3 c on c.empno=b.empno
set  table1_column=  concat(Column1,table2_column3 c.name ) 
WHERE a.column2> 100

Updating a password field using md5 encryption

We 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.
update user_mem set password=md5(password)
Substituting part of a data of a field using replace command in your SQL statement

Download sql dump of table student3
Download sql dump of table student3_total
Download sql dump of table student3_avg
Copy Table Updating multiple records by ON DUPLICATE KEY UPDATE
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    cm_mehdi

    24-01-2010

    very good
    murali

    11-03-2010

    hai everyone this is a very nice tutorial
    sanjeev

    06-07-2010

    write a update statement no procedure where we can update employee gender column value to female if it is male or to male if it is female.
    arpan katiyar

    10-04-2011

    how can i insert more than one row in sql 2008 plese send full code in asp.net
    Larry L

    02-03-2014

    I keep getting syntax errors using that. In my example, I am trying to add the contents of Field1+Feild2 to Field1.
    TableName (name of table)
    Field1 = 5
    Field2 = 2
    I want to update it so that Field1 = 5+2 in this example.
    Tried using two lines:
    UPDATE 'TableName'
    SET R1= sum(R1,R1a)


    05-06-2021

    very nice tutorials

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer