UPDATE statement in MySQL | UPDATE JOIN in MySQL

UPDATE 

The UPDATE statement is used to update or modify data in a table. It is used to change the values in one or more columns of a single row or multiple rows.

Syntax:

UPDATE table_name
SET 
column1=value1, column2=value2,...
WHERE condition;

In the above syntax:

• table_name is the name of the table that will be updated,
• name of the columns that we want to modify is written after the SET clause,
• condition is written after the WHERE clause to specify the rows that we want to modify.
• The condition can be omitted but in that case, all the rows will be modified.

1. Using UPDATE statement to update values in a single column

The above table contains the details of the customers.

UPDATE customers
SET city='Mumbai'
WHERE customer_id=1;

The modified table after the execution of the above query will be:

2. Using UPDATE statement to update values in multiple columns

The UPDATE statement can be used to modify or update multiple columns at a time.

For example:

UPDATE customers
SET city='Mumbai',mobile='8342552XXX'
WHERE customer_id=1;

The above query will update records of two columns of the row having customer_id=1. The modified table after the execution of the above query will be:

UPDATE with JOIN 

Joins are often used to query rows from a table that have or may not have matching rows in another table. UPDATE with JOIN clause is used in MySQL to update data in one table using another table and Join condition.

Syntax:

UPDATE table1, table2
[INNER JOIN/ LEFT JOIN] table1 ON table1.column1=table2.column2
SET table1.column2=table2.column2
	table2.column3=expression
WHERE condition

In the above syntax:

• table1 is the main table and table 2 is another table that will be joined with table1. We can use only one table after the update clause if we want to update the data of that table only.
• After that, the type of JOIN is specified,
• then new values are assigned to the columns of the tables whichever we want to update.
• After that, the condition is associated with the WHERE clause to limit the rows in which we want to update the data.

For example:

Suppose there are two tables ‘customers’ and ‘purchases’. ‘customers’ have the details of the customers and their bill_amount and purchases have the details of the percentage of discounts offered on a particular amount of purchase.

The above table contains the purchase details.

UPDATE customers
INNER JOIN
purchases ON customers.bill_amount=purchase.bill_amount
SET 
discount_Offered = purchase.percentage/100*customers.bill_amount;

The above-written query takes the percentage of discount from the purchase table by matching the bill_amount from the customers and Purchase tables and updates the discount_offered column in the ‘customers’ table.

The updated ‘customers’ table after the execution of the above query will be: