SQL Server Update Data

UPDATE statement in SQL Server is a DML statement used to update or modify the already existing records into a table or view. This statement is required in the real-life scenario where our data stored in the table changes regularly. This article helps to learn how the UPDATE statement works with the tables or views in SQL Server with different options.

The UPDATE query is always recommended to use with the SET and WHERE clause. We can modify or update the single or multiple columns at a time. It should note that this query does not display any result set. Also, we cannot undo the modification once it is executed. However, if we accidently do the updation, we need first to find the changes using the SELECT statement and then execute the UPDATE query by applying the same criteria. Therefore, it is advised to keep the backup copies of all the tables that help to recover the wrongly updated data.

Syntax

The following syntax illustrates the UPDATE statement in SQL Server:

Parameter Explanation

The above syntax parameter description is given below:

Example

Let us understand the UPDATE statement with the help of various examples. Suppose we have a table "Student" within the "javatpoint" database that contains the following data:

Update Single Column

If we want to update the 'Marks' of the student name 'Alan Simmons', we can do this by using the following query:

After successful execution, we will verify the table using the below statement:

We will see the below output where we can see that our table is updated as per our conditions.

Update Multiple Column

The UPDATE statement can also use to update more than one column within a single statement. For example, the below query will update the 'Age' and 'Marks' of the student name 'Diego Bennet' that contains a NULL value:

After successful execution, we will use the SELECT statement again to verify the updation. We will see the below output where we can see that our table is updated as per our conditions.

UPDATE with SQL Server Management Studio (SSMS)

We can also use the SSMS to update the table's data. The following steps explain how to update data in a table using SSMS:

Step 1: Open the SSMS and then navigate to the Object Explorer-> Database -> Tables folder. It will show all available tables.

Step 2: Select your desired table in which you want to make changes and right-click on it to get the pop menu. Now, select Script Table as -> UPDATE to -> New Query Editor Window options from the drop-down list. See the below image:

Step 3: Once we click on "New Query Editor Window", we will see the following query page:

Next, use the following command to update the table:

Executing the statement, we can see that 1 row is affected.

After execution, we can see that the above records are now updated: