SQL – Update Data in a table using sql command (syntax)

Lots of situation may rise, where we may require to change data in a table. In SQL world, changing the value of data is known as update. We are updating information or data in a table.

Why we need to update data in a table?

Roll_no Student_name Subject Marks
1 John Math 90
2 Rita Math 95
3 Sam Math 84
4 Jack Math 72
5 Pom Math 99

Lets consider, the above pictorial representation of table. (In previous chapter we have learned how to insert data into table using SQL Command, and so at this point, you must have a table in xampp server, phpmyadmin section. If not create one.)

Suppose, we got above mark sheet and asked to insert into the table. And later on, Sam and jack applied for re-evaluation of their answer sheet. And they got 92 and 84 in math respectively. So we are required to change (update) this information in the table.

So in such scenario, either we can delete the entire table, create a new one, insert all the records or we can simply change the required values. First one is time consuming, resource consuming and cost consuming process. (Why cost consuming, in real life situation, IT company charges for each and every action). The second solution is efficient solution.

This is just one example but in real life scenario, we may get lots of such situations.

SQL COMMAND TO UPDATE DATA IN A TABLE:

To change values of a table, sql is providing a command using which we can easily update the required values. In SQL world, changing a value is referred as updating a value. Using this command we can update a value of a table.

NOTE: We can not change values of a row more than one. (That is, at one time, we can change (update) value of one row only)

In our current example, we are required to change two values. So we need to execute two commands individually.

Before updating a value of a row in a table, we must have to know whose value we are going to update.

In current example, we are going to update marks value of Sam and Jack.

Lets first update Sam’s math marks.

Remember: In our example, after re-evaluation process, sam got 92 marks. So we are going to update the marks value to 92. So whose mark we are going to update, its Sam.

SQL COMMAND TO UPDATE DATA:

UPDATE TABLE_NAME SET COLUMN_NAME=VALUE WHERE COLUMN_NAME=VALUE

EXAMPLE:

Therefore the syntax would be as below

UPDATE new_student_marks SET MARKS=92 WHERE STUDENT_NAME=’sam’

NOTE: We have kept sam in single quote. Because when we provide a string (varchar) value we always keep inside single quote.

Now, launch xampp, click on the database, then click on SQL from menu panel, then inside the command box type the exact syntax, then click on ‘go’ button.

Update Data Using SQL command Update Data Using SQL command
Update Data Using SQL command

The final status message is going to appear as above. It says 1 row affected.

Update data using sql command

See sam’s mark is updated to 92.

Similar way we can also update Jack’s mark as well.

NOTE: In our example, we are aware of student name, so we have updated accordingly. For any reason, if we are aware of roll no only then also we could have updated. The command would be as below. 

update new_student_marks set marks=84 where roll_no=4

(In the above command, we are updating jack’s marks but referring to the roll no)

Because roll no 4 is a int value, thats why we have not kept it inside single quote.

Assignment:

  1. Change the name of the student to SUMAN whose roll no is 3
  2. Change the subject to science of the student who has secured 95 in math
  3. Change the roll no to 6 who has secured 92

Try to complete the above assignments. Practice as much as you can. It will help you to master SQL.