Instagram
youtube
Facebook
Twitter

MS SQL Server- Update data

MS SQL Server-Update data

  • Update statement in SQL Server is used to modify data already existing into a table or view.
  • The Update statement is used with the SET and WHERE clause.
  • We can update the single or multiple columns at a time and we cannot undo it once the update is executed.
  • There are two ways to update records.
  • Update statement Syntax in SQL Server:
UPDATE[database_name].[schema_name].table_name SET column1= new_value1,column2=new_value2,....[WHERE Clause];
  • database_name is the name of the database where the table is stored and where we are going to update data.
  • Schema_name indicates the schema to which the table belongs.
  • table_name is the name of the table in which we are going to update data.
  • SET indicates the column's names and their value to be updated.
  • WHERE clause is an optional clause that indicates the row where we want to update.

Example:

  • Update Single column
Id Name Branch CGPA
EN19CS301254 Ravi CSE 9.34
EN19CS301246 Savi CSE 8.23
EN19CS301248 Aayush CSE 7.25
EN19CS301258 Kavi NULL NULL
  • If we want to update the CGPA of the student with the name 'Ravi' in table 'student' and database 'University'.
UPDATE university.dbo.student SET CGPA=9.23 WHERE name='Savi';
  •  After execution of the following Query, verify the data using SELECT statement:
SELECT * FROM student;
Id Name Branch CGPA
EN19CS301254 Ravi CSE 9.34
EN19CS301246 Savi CSE 9.23
EN19CS301248 Aayush CSE 7.25
EN19CS301258 Kavi NULL NULL
  • Update Multiple Column
UPDATE university.dbo.Student SET Branch='CSE', CGPA=7.50 WHERE Name='Kavi';
  •  After execution of the following Query, verify the data using SELECT statement:
SELECT * FROM student;
Id Name Branch CGPA
EN19CS301254 Ravi CSE 9.34
EN19CS301246 Savi CSE 9.23
EN19CS301248 Aayush CSE 7.25
EN19CS301258 Kavi CSE 7.50

Update using SQL Server Management Studio(SSMS)

  • Open the SSMS.
  • In Object Explorer, Expand the database folder and then the table folder.
  • Select the table where you want to make changes and right-click on it. In the pop-up menu select Script Table as then Update to and then New Query Editor Window options from the list.
  • On clicking the New Query Editor Window, we will reach the following query page. Use the Query to update the table:
UPDATE [dbo].[Student]  SET [Name] = 'Ravi', [Branch] ='CSE',[CGPA]=9.86 
 WHERE [Id] = 'EN19CS301254';
  • Execute the statement and see the updated records.
Id Name Branch CGPA
EN19CS301254 Ravi CSE 9.86
EN19CS301246 Savi CSE 9.23
EN19CS301248 Aayush CSE 7.25
EN19CS301258 Kavi CSE 7.50