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:
| Parameter | Descriptions |
|---|---|
| database_name | It is the database name in which our table is stored. |
| schema_name | It indicates the schema to which the specified table belongs |
| table_name | It is the name of an existing table in which we want to perform modifications or updation. |
| SET | It indicates the column’s names and their values to be updated. If there is a need to update multiple columns, separate the columns with a comma operator by specifying the value in each column. |
| WHERE | It is an optional clause that indicates the row name in which we are going to perform updation. It is recommended to use this clause because sometimes we want to update only a single row, and if we omit this clause, the whole records of the table will be updated. |
Example
Let us understand the UPDATE statement with the help of various examples. Suppose we have a table “Student” within the “tutoraspire” 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:
