>> Update table using SQL (UPDATE / SET)

SQL  UPDATE  Table/Data:

Suppose we have a table named "EMP_INFO" as below:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
DAVID	MARKETING	2000	CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	DALLAS	HR	TX	5-Jan-06
RAM	RESOURCES	1200	DALLAS	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1100	AUSTIN	MARKETER	TX	9-Feb-08
BRITNI	TECHNICAL	1800	BALTIMORE	ASSISTANT	MD	1-Feb-09
ADAM	TECHNICAL	2200	BALTIMORE	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2500	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	ARLINGTON	MANAGER	VA	21-Aug-12

Let's suppose that we need to add 100 dollars to their salary to all the EMPLOYEEs .

Syntax :

UPDATE  {  TABLE NAME  } 

SET SALARY= SALARY+ 100;

Query:

UPDATE  EMP_INFO

SET SALARY= SALARY+ 100;

After running the query , it will display : 10 rows updated (As we have 10 rows/records in the table).

Now , let's see the data.

SELECT  *  FROM  EMP_INFO;

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
DAVID	MARKETING	2100	CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1100	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1600	DALLAS	HR	TX	5-Jan-06
RAM	RESOURCES	1300	DALLAS	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	AUSTIN	MARKETER	TX	9-Feb-08
BRITNI	TECHNICAL	1900	BALTIMORE	ASSISTANT	MD	1-Feb-09
ADAM	TECHNICAL	2300	BALTIMORE	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2600	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1300	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2300	ARLINGTON	MANAGER	VA	21-Aug-12

Let's suppose , we need to update salary for the TECHNICAL Department only  (add 100 to their salary):

Syntax :

UPDATE  {  TABLE NAME  } 

SET SALARY= SALARY+ 100

where DEPARTMENT= 'TECHNICAL' ;

Query:

UPDATE  EMP_INFO

SET SALARY= SALARY+ 100

where DEPARTMENT= 'TECHNICAL' ;

Run this query and you will see the statement : 2 rows updated (As there are  2 records for DEPARTMENT= 'TECHNICAL' ).

Now, to check the updated data:

SELECT  *  FROM  EMP_INFO;  -- below result will be displayed:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
DAVID	MARKETING	2100	CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1100	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1600	DALLAS	HR	TX	5-Jan-06
RAM	RESOURCES	1300	DALLAS	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	AUSTIN	MARKETER	TX	9-Feb-08
BRITNI	TECHNICAL	2000	BALTIMORE	ASSISTANT	MD	1-Feb-09
ADAM	TECHNICAL	2400	BALTIMORE	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2600	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1300	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2300	ARLINGTON	MANAGER	VA	21-Aug-12

if you want to update more than one column at the same time :

Suppose you want to update the Salary and Position of the employee ADAM. Let's increase his salary by 10% and his position to DEPT_HEAD from LEAD.

Query:

UPDATE  EMP_INFO

SET SALARY = SALARY + SALARY *10/100 , POSITION= 'DEPT_HEAD'

where NAME = 'ADAM' ;

When we run the query , this will show : 1 row updated ( As we have one record only for ADAM).

Now, to check the updated data:

SELECT  *  FROM  EMP_INFO;  -- below result will be displayed:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
DAVID	MARKETING	2100	CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1100	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1600	DALLAS	HR	TX	5-Jan-06
RAM	RESOURCES	1300	DALLAS	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	AUSTIN	MARKETER	TX	9-Feb-08
BRITNI	TECHNICAL	2000	BALTIMORE	ASSISTANT	MD	1-Feb-09
ADAM	TECHNICAL	2640	BALTIMORE	DEPT_HEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2600	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1300	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2300	ARLINGTON	MANAGER	VA	21-Aug-12

NEXT

Go To Main SQL Page



.