SQL Server UPDATE

SQL Server is a database engine. It works on the relational database management system (RDBMS) that allow programmer to create, manage, and manipulate data present on the relational database through their commands which is easy to learn and implement.

UPDATE Command

The UPDATE operation is a part of DML (Data Manipulation Language) in SQL Server which is used for updating the existing data in the table of the database. It is one of the basic operations that gets used frequently when there needs a change in the data of the table. We can update single as well as multiple columns based on the condition we want by using the WHERE clause. The data modifications are very easy using UPDATE Commands.

Syntax:

UPDATE table_name SET  column_name1 = value1 , column_name2 = value2,.. 
WHERE condition 

Parameters: column_name1 and column_name2 are the columns for which new values need to be assigned, the condition is the condition on which to apply the update.

Explanation: We use the SET Operator to set new values that we want to place in place of old values. Generally, we use the WHERE clause to update existing values because there are lots of records present in the table, if we want to update a particular record then we use the WHERE Clause over theirs to update the record.

How to Use the UPDATE Query?

For a better understanding of UPDATE Command with SQL Server, we need a table on which we will perform operations or queries. So here we have the CoursesActive table which contains some data as you can see in the below image. If you don’t know how to Create table in SQL Server then refer to this.

Output:

CoursesActive Table

UPDATE Command with Single Records

Let’s set the new cost for the course called ‘Data Structures And Algorithms’ in ‘CoursesActive‘ Table using UPDATE Command.

Query

UPDATE CoursesActive SET courseCost = 5000
WHERE courseName = 'Data Structures And Algorithms' 
SELECT * FROM CoursesActive

Output:

After UPDATE the cost of Data structures and Algorithms

Explanation: In this query, We have set the cost of ‘Data Structures And Algorithms’ as ‘5000’.

UPDATE Command With Multiple Records

Example: Updating the ‘courseCost’ of all the courses with a discount of 10% in the ‘CoursesActive’ table.

Query

UPDATE CoursesActive 
SET courseCost = ROUND(courseCost * 0.9,2); 
SELECT * FROM CoursesActive

Output:

After Applying discount of 10% on all cost of CoursesActive Table

Explanation: In the above Query, We have applied the discount of 10% across all the courseCost of the CourseActive table. The new courseCost values are calculated by multiplying the existing courseCost values by 0.9 (reducing them to 90% of their current values) and then rounding the result to two decimal places using the ROUND function.

Conclusion

Using theUPDATE Command in the SQL Server made it easy for the programmer to overcome their mistake as they inserted wrong information into the database or table. They can simply update their existing data with simple syntax. It ensures data integrity and data management in the relational database without harming the data or their order.