You can restore the table with data by using the SQL dump again.
DROP TABLE student
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'
UPDATE student SET class='Five' WHERE class='Four'
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.
UPDATE student SET class='Five' WHERE class='Four' and mark >= 70
|7||My John Rob||Fifth||78||60||70|
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
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=math+5, social=social+5, science=science+5 WHERE id=3
UPDATE student3 SET math=0, social=0, science=0
To Update multiple records use INSERT ... ON DUPLICATE KEY UPDATE
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 SET mark=0
Using Left Join
update student3_total,student3 SET mark= (math+social+science) WHERE s_id=id
UPDATE student3_total LEFT JOIN student3 ON s_id=id SET mark= (math+social+science)
UPDATE student3_total INNER JOIN student3 ON s_id=id SET mark= (math+social+science)
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 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
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 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
Substituting part of a data of a field using replace command in your SQL statement
update user_mem set password=md5(password)
|hai everyone this is a very nice tutorial|
|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.|
|how can i insert more than one row in sql 2008 plese send full code in asp.net|
|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:
SET R1= sum(R1,R1a)
|very nice tutorials|