SQL Update Query | SQL UPDATE Statement : Best 3 Examples

SQL UPDATE Statement

:Modifying Data in Your Database

SQL UPDATE Statement SQL Update Query UPDATE is the data manipulation DML command that is used to modify records in the database tables.

SQL Structured Query Language is the heart of the database management system that permits us to interact with databases using DDL, DML, and DCL commands. Users can manipulate the data using different SQL statements. UPDATE is one of the main fundamental commands used to change or replace data from an existing table.

UPDATE statement is a DML statement of SQL that is used to modify data from tables. We know that tables containing data is called collection of information, to update or make changes in old data from your tables, you need to write an Update statement in SQL.

We can Update data in many ways, like from tables, set fixed values, etc.; we can set default or fixed values, or we can set values from another table by using update sql statement
In this article, we will discuss details about the UPDATE statement in SQL.

SQL UPDATE

The UPDATE statement in SQL Server is used to made changes in already exist data in a table.

UPDATE TableName
SET column1 = value1, column2 = value2, ...ColumnN=ValueN
WHERE condition;

TableName: table name defines the name of the table you want to update.
SET: Indicates that you are setting new values for specific columns of the tables.
column1, column2, …: These are the names of the columns you want to update.
value1, value2, …: The new values you want to assign to the specified columns.
WHERE: An conditional or filter clause that allows you to specify which records to update. If where clause is not used, then all records in the table will be updated.

Updating a Single Column

While updating any data make sure take backup for data which you are updating or you can take that specific tables backup using Generate Scrip option in SQL server. taking backup before update will be helpful to restore the data. or you can take whole database backup
Before writing update script make sure to write Select query for the same record for which you are updaing or changing values

Suppose we have a table called EmpMaster with the following data:

now we have to change the mobile number of JOY ROBO from 9860587452 To 9595959595

then write select query for JOY ROBO first

select * from EmpMaster where ID=502;

the output will look like following data for JOY Robo.

To change the JOY ROBO ‘s Mobile number we can use the UPDATE statement as follows:

update EmpMaster set Mobile='9595959595' where ID= '502';

This UPDATE SQL query will change JOY ROBO ‘s Mobile number from 9860587452 To 9595959595, and the table will now look like this:

if you select the records for JOY ROBO only the output will be looks like below now Mobile number of JOY ROBO is chagned to 9595959595

Updating Multiple Columns

You can update multiple columns only in one sql UPDATE statement. Suppose we want to change smit yaK’s Email and Address

select * from EmpMaster where ID=503
select * from EmpMaster where Email='yak@gmail.com' and Address='Delhi' and ID=503;

here both select statements output will be same.

To update multiples columns in table you can write SQL update statement like below here we have updated two different columns Email and Address and set new values to both the columns

update EmpMaster set Email='smit.yak@rediff.com',Address='Nagpur' 
where Email='yak@gmail.com' and Address='Delhi' and ID=503;

Output of the table for 503 id will be looks like below

This query will update both the Email and Address columns for the EmpMaster table with ID =503.

Updating All Rows

If you want to update all rows in a table, simply remove the WHERE clause. Be serious when doing this, as it can affect a huge records.
and once you update the all records it will not reverse or undo.
Make sure to take backup of your table before you update this will be helpful to restore as like previous data after update

Table Data before Update Salary

suppose we have to Increase all employee’s Salary by 12 % then we can write SQL Update Statement like below

Update EmpMaster set Salary=Salary+(Salary*12)/100 ;

This will update All employees salary with 12 Percent extra than current salary

here null value in salary column will not change this will remain same as it is NULL

Update from Another Table

You can update the columns and set values to column from another table.

make sure that to update from another table values you must have to join the both the tables for updating matching records from EmpMaster and Employee
here i have ID and EmpolyeeID colums are there in table thriugh which i can join bothe tables

We have to Update EMployeeSalary column from Employee Table whos EmployeeSalary is NULL and we will set values from EmpMaster

First Write SQL select Query with the same conditions to verify which rows will be affected.

select EmployeeId,EmployeeName,EmployeeSalary from Employee a inner join EmpMaster b on a.EmployeeId=b.ID;

suppose you have to update all employees salary from EmpMaster table to Employee table then you can write an SQL Update Statement like below

update Employee set EmployeeSalary =b.Salary from EmpMaster b  where EmployeeId=b.ID;

Output of updated Employee salary column will be looks like below

one column from Employee is null because it was already null in EmpMaster table so i updated date here as UPDATe

UPDATE Without writing Query

you can modify the data without writing SQL Query to change data without any sql script you needs an SQL Dbx Editor, in sql dbx editor you can modify data without writing sql statement To download SQL Dbx click the below link.

http://www.sqldbx.com/

suppose you have to modify the EmpMaster Table in that table you have to change the Address of the ID =501

How to modify data without Update query

• Download SQL DBX Personal edition its free
• After download extract it
• Then open SqlDbx and enter Select DBMS type i.e SQL server or Oracle server or My Slq etc
• After selecting server type then enter your server name/ instance name or ip address of server
• And then enter your database user name and password and click on ok
• write select query to display data in result
• Then right click on the column which you want to modify and select Modify EmpMaster Data and then coulm will be editable you can change your address manually without writing update query

• ^{When using the UPDATE statement in SQL, It is mandatory to follow the below guidelines.}

1. Use Transactions: Wrap your UPDATE statements in transactions when making multiple updates to ensure data consistency.
2. Always use WHERE Clause: if you want to update whole columns data then not necessary to use where clause but make sure to use WHERE clause in for specific records update apply condition while writing update query, this will prevents unnecessary data modifications.
3. Backup Data: Before making any updates, especially in production environments, take a backup of the data or tables to avoid data loss.
4. Before modifying data make sure to take a backup of table or database
5. If you want to update a single column and forget to include a where clause, it’s important to have a backup that you can revert to in case your entire data is modified.
6. Test Queries: Test your UPDATE queries in a UAT environment or use a
7. SELECT statement with the same conditions first to verify which records will be affected.
8. Be Careful with Arithmetic Operations: When using mathematical operations in your SET clause (e.g., increasing salaries by a percentage), double-check the results to avoid unexpected outcomes.

https://itdevelopement.com/

The SQL UPDATE statement DML Command used for modifying data in relational databases.
Be serious while UPDATE, especially in production environments, to avoid unnecessary modifications and data loss.