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:
1. UPDATE [database_name].[ schema_name].table_name
2. SET column1 = new_value1,
3. column2 = new_value2, ...
4. [WHERE Clause]
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 "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:
1. UPDATE Student
2. SET Marks = 492
3. WHERE Name = 'Alan Simmons';
After successful execution, we will verify the table using the below statement:
1. SELECT * FROM Student;
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:
1. UPDATE Student
2. SET Age = 28, Marks = 492
3. WHERE Name = 'Diego Bennet';
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:
1. UPDATE [dbo].[Student]
2. SET [Name] = 'Peter Huges'
3. ,[Gender] = 'Male'
4. ,[Age] = 26
5. ,[Marks] = 478
6. WHERE [Id] = 1
Executing the statement, we can see that 1 row is affected.
After execution, we can see that the above records are now updated:
AD
