Hi Friends ,

Today We are going to look how to write UPDATE SQL Query in Real Time

Lets create a Table  with name as  EmpDetails and Insert Random Data into it.

Create Table EmpDetails (Empid varchar(10),Salary int)

INPUT:

Insert into EmpDetails (Empid ,Salary )

values ('E100','20000'),
('E101','30000'),
('E102','50000'),
('E103','70000'),
('E104','100000')

OUTPUT:

Message

5 Rows affected



Now ,if one of the Employers,Suppose E102  Salary to be updated  from 50000 to 60000,

Then

Typical DML Query to Update Salary:

Check the number of Records that will be effected by Update:

Select * from EmpDetails  where Empid ='E102'

Results  Meassges

    Empid    Salary
1  E102       60000


So, One row will be effected

INPUT:

Update  EmpDetails  set Salary ='60000' where Empid ='E102'


OUTPUT:

Messages

(1 row(s) affected)

So,One row is updated.


You Must have Observed that at first we have checked the number of rows that will be updated by
using SELECT query and later we have UPDATED the table.Instead of two queries we can combine them in ONE QUERY .


REAL TIME DML query for UPDATION:

Here  A is aliase name given to Table EmpDetails

Update A set Salary ='60000'
---select *
From EmpDetails   A  where Empid ='E102'


Running Select Query at first after comment:


Update A set Salary ='60000'
---select * 
From EmpDetails   A  where Empid ='E102'


OUTPUT:

Results  Messages

      Empid  Salary

1   E102     50000

We observed that one row  will be affected by Update.Lets Execute Total update query now.


Update A set Salary ='60000'
---select * 
From EmpDetails   A  where Empid ='E102'

OUTPUT:


(1 Row(s) affected)


Checking the Updated Record:

Update A set Salary ='60000'
---select * 
From EmpDetails   A  where Empid ='E102'




OUTPUT:

Results  Messages

      Empid   Salary
1    E102      60000

So ,The Employers Salary has been updated from 50000 to 60000.


Rather than writing Two separate DML commands(Select ,Update),we can combine them as One which will prevent unwanted updates  and  reduce our effort especially in REAL TIME scenarios.