How to UPDATE statement with JOIN in SQL? [SOLVED]

Different methods to perform SQL UPDATE with JOIN

SQL join clauses are commonly used to query data from related tables, such as an inner join or left join. SQL update statement is used to update records in a table but a cross-table update can be performed in SQL Server with these join clauses.

A SQL update with join is a query used to update the data in a table based on data in another related table. The join is used to associate records in one table with records in another table, based on common columns between them.

There are two ways to perform update with join

• Update with LEFT OUTER JOIN
• Update with INNER JOIN

Syntax to do an UPDATE statement with SQL JOIN

UPDATE
Table1
SET
Table1.column1 = table2.column2,
Table2.column2 = newvalue,
...
FROM
Table1
[INNER | LEFT] JOIN table2 ON table1.common_column=table2_common_column
WHERE
[condition];

Here,

• table1,table 2: Specify the table name to be used in join to update
• common_column : The statement  joins Table1 with Table2 based on the value of common_column in both tables.
• INNER JOIN or LEFT JOIN  : The join type (INNER JOIN or LEFT JOIN) determines the type of join to be performed between the two tables.
• The WHERE clause specifies a condition to be met before the updates occur.

Note that the update statement updates Table1.column1 to be equal to the value of Table2.column2 also updates Table1.column1 with a constant value or expression.

 

Setup Lab Environment

Consider hospital management database with four tables patient, doctor, bill and laboratory to perform practical examples

Patient table

Doctor table

Bill table

Laboratory table

 

SQL UPDATE with LEFT OUTER JOIN and WHERE condition

Write SQL query to update room charges of the patients who are suffering from disease ‘heart failure’ to 600

UPDATE  bill SET    room_charge = 600
FROM  bill LEFT OUTER JOIN
patient ON bill.patient_id = patient.patient_id
WHERE (patient.disease = 'heat failure')

• In the above query , SQL update statement is used to update the "room_charge" column in the "bill" table.
• The update is performed on the rows where the patient has a "heart failure" disease, as determined by a join between the "bill" and "patient" tables on the "patient_id" column.
• The "LEFT OUTER JOIN" is used to include all rows from the "bill" table, even if there is no matching row in the "patient" table.
• The value of the "room_charge" column is set to 600 for all matching rows.

OUTPUT:

To view the output of above query, we need to use SQL select statement to retrieve data from bill table

SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM   bill

 

Example-1: Set column value to another table column value

Write SQL query to update  value of doctor charges in the doctor table by retrieving it from bill table

UPDATE doctor SET doctor_charges = bill.doctor_charge
FROM doctor 
LEFT OUTER JOIN bill ON bill.doctor_id = doctor.doctor_id

• In the above query, SQL Update statement is used to updates the "doctor_charges" column of the "doctor" table with the values from the "doctor_charge" column of the "bill" table.
• The "UPDATE" clause specifies the table that will be updated, in this case the "doctor" table.
• The "SET" clause specifies what the update will be; in this case, the "doctor_charges" column of the "doctor" table will be set to the values in the "doctor_charge" column of the "bill" table.
• SQL LEFT OUTER JOIN is used to join two table bill and doctor , doctor is a left table whereas bill is the right table . In this case, the join is based on the "doctor_id" column, which is common to both tables, and the "ON" clause specifies the join condition.

OUTPUT:

To view the output of above query, we need to use SQL select statement to retrieve data from doctor table

SELECT  doctor_id, name, age, gender, address, doctor_charges
FROM   doctor

 

Example-2: Update multiple column value

Write SQL query to update doctor charges and room charges in bill table increase by 100

UPDATE   bill SET  doctor_charge = bill.doctor_charge + 100, room_charge = bill.room_charge + 100
FROM  bill 
LEFT OUTER JOIN doctor ON bill.doctor_id = doctor.doctor_id;

• In the above query, SQL update statement is used to updates the values  of a table named "bill” by increases the values of two columns in the table, "doctor_charge" and "room_charge" , by 100 each.
• The query uses a LEFT OUTER JOIN between the "bill" table and another table named "doctor", based on the "doctor_id" column.
• In the SET clause of the query, the new values of the "doctor_charge" and "room_charge" columns are calculated by adding 100 to their current values, which are referenced using the "bill" table name. The update will be applied to all records in the "bill" table.

OUTPUT:

To view the output of above query, we need to use SQL select statement to retrieve data from bill table

SELECT   bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM  bill

 

SQL UPDATE WITH INNER JOIN and where condition

Write SQL query to update room charges of the patients who are suffering from disease ‘heart failure’ to 600

UPDATE  bill SET  room_charge = 600
FROM  bill 
INNER JOIN patient ON bill.patient_id = patient.patient_id
WHERE (patient.disease = 'heat failure')

• In the above query, SQL update statement is used to updates a table named "bill" and sets the value of the "room_charge" column to 600 for all records in the "bill" table that meet a certain condition.
• The condition is defined by a join operation between the "bill" table and the "patient" table, based on the equality of the "patient_id" column in both tables. The join is performed using the "INNER JOIN" keyword, which returns only the rows from both tables where there is a match between the "patient_id" columns.
• Finally, the "WHERE" clause specifies that the update should only be applied to the rows where the "disease" column in the "patient" table has a value of "heat failure".

OUTPUT:

To view the output of above query, we need to use SQL select statement to retrieve data from bill table

SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM bill

 

Example-1: Setting column value to another table column value

Example 5: Write SQL query to update  value of doctor charges in the doctor table by retrieving it from bill table

UPDATE  doctor SET  doctor_charges = bill.doctor_charge
FROM  doctor 
INNER JOIN bill ON bill.doctor_id = doctor.doctor_id

• In the above query, SQL update statement is used to updates the doctor_charges column of the doctor table with the values from the doctor_charge column of the bill table.
• The update is done based on a join condition between the two tables, doctor and bill. The join is specified with the INNER JOIN keyword and is established on the doctor_id column of both tables, where bill.doctor_id = doctor.doctor_id.
• This query updates the doctor_charges column of the doctor table with the corresponding values from the doctor_charge column of the bill table where the values in the doctor_id column of both tables match.

OUTPUT:

To view the output of above query, we need to use SQL select statement to retrieve data from doctor table

SELECT  doctor_id, name, age, gender, address, doctor_charges
FROM doctor

 

Example-2: Update multiple column value

Write SQL query to update doctor charges and room charges in bill table increase by 100

UPDATE  bill SET doctor_charge = bill_1.doctor_charge + 100, room_charge = bill_1.room_charge + 100
FROM bill AS bill_1 
INNER JOIN  doctor ON bill_1.doctor_id = doctor.doctor_id CROSS JOIN bill;

• In the above query, SQL update statement is used to updates the values of a table named "bill” by increases the values of two columns in the table, "doctor_charge" and "room_charge" , by 100 each.
• The query uses a INNER JOIN between the "bill" table and another table named "doctor", based on the "doctor_id" column.
• In the SET clause of the query, the new values of the "doctor_charge" and "room_charge" columns are calculated by adding 100 to their current values, which are referenced using the "bill" table name. The update will be applied to all records in the "bill" table.

OUTPUT:

To view the output of above query, we need to use SQL select statement to retrieve data from bill table

SELECT   bill_no, patient_id, doctor_charge, room_charge, no_of_days
FROM   bill

 

Summary

This article covers SQL update with join overview, methods for updating with join, syntax of SQL UPDATE with JOIN with INNER JOIN and LEFT OUTER JOIN, and practical examples of SQL UPDATE WITH JOIN, including SQL UPDATE with LEFT OUTER JOIN and INNER JOIN with where condition, setting values from another table, updating multiple columns.

 

References

SQL LEFT OUTER JOIN
SQL INNER JOIN

 

Read More

SQL UPDATE