To store this data we will change the length of field to 32 char. We can update the password field with md5 encryption. WHERE a.column2> 100 Updating a password field using md5 encryption Set table1_column= concat(Column1,table2_column3 c.name ) (select class, avg(average) as number fromĪs b on a.class=b.class set a.average_class=b.number Update with LEFT join using more than two tables UPDATE table1 a Now we will store the class average against each student by using GROUP BY Query. We used LEFT JOIN to join the same table and get the average mark of each student. LEFT JOIN (select id, sum(social + math + science )/3 as number You can download the MySQL dump of the table with sample data here. In another column we will store the average mark of each class for all the students. The average mark of each student in three subjects will be stored in a new column. We will create a table with student marks in three subjects. Mark= (math+social+science) Update same table with average value UPDATE student3_total INNER JOIN student3 ON s_id=id SET UPDATE student3_total LEFT JOIN student3 ON s_id=id Update student3_total,student3 SET mark= (math+social+science) Now let us update this mark column of student3_total table with sum of subject marks of student3 table. Let us first change the total mark of all students to zero. Student mark table has two columns, s_id stores the student id and mark column stores total mark of the student. Both the tables will have student id field which we will be using to link both tables. We can collect only the test data and keep them in a separate table. Let us say we have one table where students test marks are stored along with other details in other columns. The second table columns gets updated by taking data from first table. SQL UPDATE query to update columns in multiple tables using INNER & LEFT JOIN with GROUP BY query ON DUPLICATE KEY UPDATE Updating second table with data from first table UPDATE student3 SET math=0, social=0, science=0 Without using Where clause we can update the total table with new data. UPDATE student3 SET math=math+5, social=social+5, science=science+5 WHERE id=3īy adding a Where condition we are restricting updating to one record only. Let us increase the mark of Arnold ( id =3 ) by 5 in all subjects UPDATE student3 SET math=50, social=60, science=55 WHERE id=4 Let us update the new marks for Krish Star ( id =4) You can download sql dump of this table at the end of this tutorial.ĭownload the SQL dump of this table at the end of this page. We will use one more student table where we will store mark obtained by students in three subjects. Depending on the logic requirement OR combination can be added to the WHERE clause to UPDATE the records. This way we can continue with adding more AND combinations to the query. We have added two conditions by using AND as a logical operator. This will only change the records for which class=four and mark is more than or equal to 70. UPDATE student SET class='Five' WHERE class='Four' and mark >= 70 You may have to restore the original table by using the SQL dump of student table given above. We will promote those students only who has got more than or equal to 70 mark. We will change records for which class is equal to 'Four' and mark is more than 70. Now let us move one more step and change the records selectively based on some value in some other field. So this way we can update records selectively. This command will update only those records for which class is equal to 'Four'. Related Tutorial Replace part of data Appending string data No.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |