13 - SQL Update records of a table

We can update the contents of the table using an UPDATE statement. This is useful for any correction or modification of any data in tables.  Consider a situation  in the online shopping website, where the customer has once completed his profile and saved it, then the profile data would be stored in respective table, but next  time he logs in he realizes he has made some mistake and rectifies it and saves again. Now the change should reflect in table as well. That’s when UPDATE statement is used.

Syntax:

 

UPDATE table_name

SET col11= val1, col2=val2,….colN=valN

Where condition;

Here conditions can be like the ones we use in SELECT statement.

EMP_ID

EMMP_NAME

AGE

PHONE_NUM

DEPT_ID

SALARY

1

john

35

100233023

2

10000

2

Linda

30

100234565

1

15000

3

Max

40

122222344

3

22000

4

Will

40

 12323424

3

31000

5

Michal

45

12323434

3

5000

Example 1:

Update 1 row of employee table. Change the Dept_ID of employee “Linda” to 2.

 

mysql> UPDATE EMPLOYEE

    -> SET DEPT_ID  = 2

    -> WHERE EMMP_NAME = "Linda";

Query OK, 0 rows affected (0.33 sec)

Rows matched: 1  Changed: 0  Warnings: 0

Now check the results using SELECT statement, you would notice only 1 row has been affected.

mysql> SELECT EMP_ID, EMMP_NAME,DEPT_ID FROM EMPLOYEE;
+------------------+---------------------------+------------------+
| EMP_ID     | EMMP_NAME     | DEPT_ID     |
+------------------+---------------------------+------------------+
|      1     | john          |       2     |
|      2     | Linda         |       2     |
|      3     | Max           |       3     |
|      4     | Will          |       3     |
|      5     | Michal        |       3     |
+------------------+---------------------------+------------------+
5 rows in set (0.01 sec) 

Example 2:

What would be the result of below Statement?

 

UPDATE EMPLOYEE

SET DEPT_ID = 4;

Solution:

As no condition mentioned, all the rows for the dept_id would be changed to 4.  

+-----------+------------------------+-----------------+
| EMP_ID | EMMP_NAME     | DEPT_ID     |
+-----------+------------------------+-----------------+
|      1 | john          |       4     |
|      2 | Linda         |       4     |
|      3 | Max           |       4     |
|      4 | Will          |       4     |
|      5 | Michal        |       4     |
+-----------+------------------------+-----------------+
5 rows in set (0.00 sec)

Example 3:

Update multiple columns in a table.

Considering scenario where all the employees of the table have moved to department 1 and their salary changed to 15000 each.  

 

UPDATE EMPLOYEE1

SET DEPT_ID = 1, SALARY = 15000;

 

 
mysql> SELECT EMP_ID, EMMP_NAME, DEPT_ID, SALARY FROM EMPLOYEE1;
+-----------+------------------------+-----------------+-------------+
| EMP_ID | EMMP_NAME     | DEPT_ID     | SALARY    | 
-----------+------------------------+-----------------+-------------+
|      1 | john          |       1     |  15000    |
|      2 | Linda         |       1     |  15000    |
|      3 | Max           |       1     |  15000    |
|      4 | Will          |       1     |  15000    |
|      5 | Michal        |       1     |  15000    |
+-----------+------------------------+-----------------+-------------+
5 rows in set (0.00 sec)

Example4:

Update records of 1 table using data of second table.

Consider the vendor area in the customer table is incorrect, and have to update the vendor area using from the Vendor list.

CUSTOMER:

Cust_ID

Cust_Name

Cust_Area

Vendor_Id

Vendor_Area

1

Mike

OH

112

CA

2

Smith

CA

112

CA

3

Linda

OH

113

OH

4

Max

PN

114

PN

VENDOR list

Vendor_Id

Vendor_name

Vendor_area

111

IBOOM

CA

112

TRAVELO

OH

113

FLYER

CA

114

SCC

PN

 The solution would be:

 

UPDATE CUSTOMER

SET customer.vendor_area = (SELECT vendor_list.vendor_area

                                                           FROM Vendor_list

                                                                WHERE  vendor_list.vendor_area = customer.vendor_area);

This will result in change to vendor area in customer table as below:

Cust_ID

Cust_Name

Cust_Area

Vendor_Id

Vendor_Area

1

Mike

OH

112

OH

2

Smith

CA

112

OH

3

Linda

OH

113

CA

4

Max

PN

114

PN

Example 5:

Updating multiple tables.

Consider 2 tables in a vendor management system, 1 vendor details table like price, products, status and other table containing the vendor details like address, phone numbers, areas etc.

Now if there has been recent change to 1 vendor (LIKO) status and area then we can get that done in a single query as below.

 

UPDATE VENDOR_PRICING,VENDOR_DETAILS

SET VENDOR_STS = “ACTIVE”,

            VENDOR_AREA  = “MA”

WHERE VENDOR_PRICING.VENDOR_ID = VENDOR_DETAILS.VENDOR_ID AND

                        VENDOR_PRICING.VENDOR_ID = “LIKO”

 

 

Like us on Facebook