Which SQL Command is Used to Change the Data in the Rows of a Database Table

To change the data in the rows of a database table, we can make use of the UPDATE statement in SQL. The SQL UPDATE command changes the existing data in one or more records of a table.

sql-update-command

After the data has been stored in the database table, it is very habitual that at some point in the future there will be a need of updating specific information in the dataset and so for that purpose, the UPDATE command can be used.

Let's understand more about it :

The UPDATE command is a DML (Data Manipulation Language) statement. Data in one or more columns can be manipulated based on a specific set of criteria using the UPDATE command. An ALTER command is a DDL (Data Definition Language) statement and is used to alter the structure or the schema of a table in a database.

In the next article, we will dive deeper into the ALTER command. We will take a close look at SQL's UPDATE command in this post.

Note :
The UPDATE command can be used along with the WHERE clause to specify the record(s) that should be updated. If you miss out on the WHERE clause, all records in the table will be updated!

Let’s see the syntaxes for the "UPDATE" command in SQL :

Syntax for SQL UPDATE Statement :

Here, table_name specifies the name of the table, and the SET keyword is used to set the value of a specific column mentioned by column_name to the newly assigned value represented as value in the above syntax.

Example of SQL Command to Change the Data in the Rows of a Database Table :

Suppose, you have a student_details table, having ROLL_NO, FIRST_NAME, LAST_NAME, BRANCH, and MARKS as the fields, and some data has been stored in these fields.

Now, let's see how our table's data looks by making use of the SELECT command.

Note :
represents all the columns, which means we are selecting or retrieving all the data from the given table.

Output :

ROLL_NO	FIRST_NAME	LAST_NAME	MARKS	BRANCH
1	Rita	Goyal	67	CSE
2	Kanika	Mittal	91	IT
3	Jyoti	Sharma	21	CSE
4	Reshma	Kumari	100	IT
5	Jayana	Meena	32	Civil
6	Devansh	Kumar	71	Civil
7	Kareena	Bhatt	100	IT

Suppose you want to update the marks of the student having roll number 2, then the syntax of the UPDATE command in SQL would be :

Now, we can again display the student_details table to see the updated marks for the student having a roll number of 2.

Output :

ROLL_NO	FIRST_NAME	LAST_NAME	MARKS	BRANCH
1	Rita	Goyal	67	CSE
2	Kanika	Mittal	115	IT
3	Jyoti	Sharma	21	CSE
4	Reshma	Kumari	100	IT
5	Jayana	Meena	32	Civil
6	Devansh	Kumar	71	Civil
7	Kareena	Bhatt	100	IT

As we can see from the output table that the marks of the student having roll number 2 are updated from 91 to 115.

Syntax for SQL UPDATE Statement to Update Multiple Columns :

Now, let's suppose you want to update the values for multiple columns, in that case, you can mention the names of all the columns (separated by commas) for which you want to change the values along with the new values which are to be assigned.

For this, you can run the query :

Here, we have specified multiple columns (column_name1, column_name2, … are the columns considered to be updated) for which we have to apply the UPDATE command along with the values (value1, value2, ... are the new values to be assigned to the respective columns) which have to be assigned.

Suppose you want to update the marks and the branch of the student having roll number 4, then the syntax of the UPDATE command in SQL would be :

For this, you can run the query :

We can now display the student_details table to see the updated results.

Output :

ROLL_NO	FIRST_NAME	LAST_NAME	MARKS	BRANCH
1	Rita	Goyal	67	CSE
2	Kanika	Mittal	115	IT
3	Jyoti	Sharma	21	CSE
4	Reshma	Kumari	72	Electronics
5	Jayana	Meena	32	Civil
6	Devansh	Kumar	71	Civil
7	Kareena	Bhatt	100	IT

As we can see from the output table, the marks of the student having roll number 4 is updated from 100 to 72, and the branch from 'IT' has been updated to 'Electronics'.

Syntax for SQL UPDATE Statement to Update All the Rows :

We can suppose a case in which you want to update the value in a column for all the rows, in that case, you can simply remove the where clause and mention the name of the column for which you want to change the value along with the new value to be assigned.

For this, you can run the query :

Here, we have specified the column name for which we have to apply the UPDATE command along with the value to be assigned.

Suppose you want to update the branch of all the students to Chemical then the syntax of the UPDATE command in SQL would be:

We can now display the student_details table to see the updated results.

Output :

ROLL_NO	FIRST_NAME	LAST_NAME	MARKS	BRANCH
1	Rita	Goyal	67	Chemical
2	Kanika	Mittal	115	Chemical
3	Jyoti	Sharma	21	Chemical
4	Reshma	Kumari	72	Chemical
5	Jayana	Meena	32	Chemical
6	Devansh	Kumar	71	Chemical
7	Kareena	Bhatt	100	Chemical

As we can see from the output table, the branch of all the students has been updated to Chemical.

Conclusion

Update command is a data manipulation command which is used to modify the data in the records of a table.
It can be used to update a single row or multiple rows based on the conditions provided by the user.
Conditions can be specified by using the WHERE clause along with the UPDATE command in SQL.
It is important to remember that without adding the WHERE clause, all the records from the specified columns will be updated.
UPDATE command in SQL is used along with the SET clause to place new values in the specified columns.
The UPDATE command is very important as the data in any dataset keeps changing and so by making use of the UPDATE command we can keep our information up to date.

Which SQL Command is Used to Change the Data in the Rows of a Database Table ?

Syntax for SQL UPDATE Statement :

Example of SQL Command to Change the Data in the Rows of a Database Table :

Syntax for SQL UPDATE Statement to Update Multiple Columns :

Syntax for SQL UPDATE Statement to Update All the Rows :

Conclusion