Update in database refers to modifying the data of the tables. Update is a DML operation. Update command can update:
- All rows at once.
- Particular rows based on some conditions.
- Particular fields defined in the update command.
- Update tables using JOIN.
The syntax of basic update command is:
UPDATE TableName SET column1=NewValue1,column2=NewValue2, column3=NewValue3 where conditions;
NOTE: Always use where clause in update statement otherwise It will update the whole table. MySQL also provides a variable to control this situation. This variable is called ‘i-am-a-dummy-flag’. It ensures the MySQL engine to refuse all UPDATE and DELETE commands where ‘WHERE‘ condition is not present.
Updating all rows of the table
All rows of the table are updated at once, this is generally used for new columns or flag values. We can run this update command with where condition that is always true like where 1=1.
For example:
We have a data of the company names along with their address and price of some server.
ID | Company | Address | Amount |
1 | HCL | NY | $1000 |
2 | Apple | Chicago | $2000 |
3 | IBM | INDIA | $3000 |
4 | Singapore | $15000 |
UPDATE TABLE company SET Amount= ‘$4000’ where 1=1;
Here company is the table name and Amount is the column name that needs to update by value $4000 for all the tuples of the table.
After updating the table, data will be:
ID | Company | Address | Amount |
1 | HCL | NY | $4000 |
2 | Apple | Chicago | $4000 |
3 | IBM | INDIA | $4000 |
4 | Singapore | $4000 |
Now the update query modify the values of column based on some condition given in the WHERE clause.
For example, If we want to change the Address of IBM to Florida then the Update query will be:
UPDATE TABLE company SET Address=’Florida’ where Company=’IBM’ ;
After updating the table, the data becomes:
ID | Company | Address | Amount |
1 | HCL | NY | $4000 |
2 | Apple | Chicago | $4000 |
3 | IBM | Florida | $4000 |
4 | Singapore | $4000 |
Updating Multiple columns by single command
This needs to SET multiple column in the UPDATE command. For example, If we want to modify the Address and the amount of the company Google to ‘USA’ and ‘$5000’ then the query will be.
UPDATE company SET Address=’USA’, SET Amount=’$5000’ where company =’Google’;
After updating the table, data becomes:
ID | Company | Address | Amount |
1 | HCL | NY | $4000 |
2 | Apple | Chicago | $4000 |
3 | IBM | Florida | $4000 |
4 | USA | $5000 |
UPDATE TABLE company SET Amount =’$3000’ LIMIT 2;
Update Tables using JOIN
When we want to update the data of two or more tables OR the data of one table using the information from the two or more tables then use JOIN in UPDATE command.
For example:
When we need to update the Address of user name Aman to INDIA then we have to use the JOIN on the basis of common column ID.
UPDATE TABLE B INNER JOIN A on A.Id = B.Id SET B.Address=’INDIA’ where A.Name=’Aman’;
After updating the table, data will be:
Here Table B column Address updated to INDIA correspond to ID 1 in table A of Aman.