10 - MySQL Update

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

Google

Singapore

$15000

Now the update query to change the Amount of all the rows:

       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

Google

Singapore

$4000

Updating Particular rows based on some Conditions

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

Google

Singapore

$4000

We can use multiple conditions in the WHERE clause separated by any operator like AND / OR / Between / Greater then / Less then etc..

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

Google

USA

$5000

NOTE: You can also apply LIMIT on the Update Query. For example- The below query updates only first two rows.

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.

Like us on Facebook