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”
|