SQL UPDATE from SELECT statement [SOLVED]

Overview of SQL UPDATE from SELECT

SQL update statement is used to alter existing values in a record or in multiple records of a table, We can update all records of a table or limit the affected records for the update using the WHERE clause in SQL update statement but what if we need to update the record based on another table value of the same database or if we need to update same column values with different values? To solve these issues we can use any one of the methods from the following three methods.

 

Different methods to UPDATE from a SELECT in SQL Server

  1. Using the SELECT Subquery Statement
  2. Using the INNER JOIN
  3. Using the MERGE statement

 

Setup Lab Environment

Consider the hospital management database with four tables to perform practical examples of SQL update using the SELECT subquery statement

Advertisement

Patient table

SQL UPDATE from SELECT statement [SOLVED]

Doctor table

SQL UPDATE from SELECT statement [SOLVED]

Laboratory table

SQL UPDATE from SELECT statement [SOLVED]

 

Method-1: Using the SELECT Subquery Statement

SQL update using SQL select subquery is the simple and straightforward method to update the existing table record with the value retrieved from any other table of the same database using SQL select subquery, subquery is the inner query or nested query written inside any DML statement

 

Syntax

Syntax of SQL update using the SELECT subquery statement

UPDATE update_table_name
SET update_column_name=
(SELECT select_column_name FROM select_table_name WHERE select_condition ) 
WHERE update_condition;

Here,

Advertisement
  • update_table_name: Specifies the name of the table of which records should be updated
  • select_table_name: Specifies the name of the table from which value should be retrieved
  • update_condition: Specifies condition based on which record will be updated in update_table
  • select_column_name: Specifies condition based on which record will be retrieved from select_table

 

Example-1: SQL update single value from SELECT

Write SQL query to update ‘Navasari’ city patient’s doctor_id with doctor_id of doctor who is living in ‘Navasari’

UPDATE  patient
SET  doctor_id =
(SELECT  doctor_id FROM doctor WHERE (address = 'Navsari'))
WHERE        (address = 'navsari');
  • In the above query , SQL update statement is applied on patient table to modify doctor_id of patient whose city name is ‘Navsari’
  • SQL select subquery is applied to retrieve doctor_id of doctor who lived on ‘Navasari’ city

OUTPUT:

To view the result of the above query we need to retrieve records from patient table using SQL select statement

SELECT  patient_id, name, age, gender, address, disease, doctor_id  FROM  patient

SQL UPDATE from SELECT statement [SOLVED]
After executing above query,

Patient ‘rahul’ with the address as ‘navasari’, doctor_id value has been updated from 21 to 24

 

Example-2: SQL update multiple values from SELECT

Write SQL query to update patient_id and doctor_id in laboratory table data with patient_id and doctor_id of ‘Navasari’ city respectively if the billing amount is greater than or equal to 800

Advertisement
UPDATE  laboratory
SET  patient_id = (SELECT  patient_id  FROM  patient WHERE        (address = 'Navasari')), doctor_id =
(SELECT   doctor_id FROM  doctor WHERE (address ='Navsari'))
WHERE (amount >= 800);
  • In the above query, SQL update statement is used to update laboratory table data , update patient_id and doctor_id with the patient_id and doctor_id of patient and doctor table who are living in ‘Navasari’ city  if the laboratory bill amount is more than or equal to 800
  • SQL select subquery is used to retrieve patient and doctor_id from patient and doctor table respectively

OUTPUT:

To view the result of the above query we need to retrieve records from the laboratory table using SQL select statement

SELECT  lab_no, patient_id, doctor_id, date, amount
FROM     laboratory;

SQL UPDATE from SELECT statement [SOLVED]

 

Method-2: SQL Update from SELECT using SQL INNER JOIN

In SQL update from SELECT using INNER JOIN method, the table records to be updated will be joined with the reference table that contains updated record values. So that, the record in the targeted table will be updated based on the specified join type.

 

Syntax

UPDATE  update_table_name
SET update_column1 = select_column1, update_column2 = select_column2
FROM update_table_name
INNER JOIN select_table_name ON update_table_reference_column_name=select_table_reference_column_name

With the SET keyword, we specified which columns of the update table we want updated and set them to equal the updated values found from the select table.

Finally, we linked the source and target tables via an INNER JOIN by using the matching columns from update_table_reference_column and select_table_reference_column_name respectively

 

Example-1: SQL update single value from SELECT

Write SQL query update doctor_id of patient table with the doctor_id of doctor table if the patient and doctor are living in ‘Navsari’

Advertisement
UPDATE  patient  SET   doctor_id = doctor.doctor_id FROM patient 
INNER JOIN doctor ON patient.doctor_id = doctor.doctor_id
WHERE (patient.address = 'Navsari') AND (doctor.address = 'Navsari')
  • In the above query, SQL update statement is used to modify doctor_id of patient table with the doctor_id of doctor table if patient and doctor are living in ‘Navsari’ city
  • SQL inner join is used to join both tables patient and doctor for retrieving new updating value of doctor_id from doctor table

OUTPUT:

To view the result of the above query we need to retrieve records from patient table using SQL select statement

SELECT  patient_id, name, age, gender, address, disease, doctor_id FROM  patient;

SQL UPDATE from SELECT statement [SOLVED]

 

Example-2: SQL update multiple values from SELECT

Example 4: Write SQL query to update doctor_id and date in patient laboratory data with doctor_id of ‘Navasari’ city and date as 3rd march 2000 respectively if the billing amount is greater than or equal to 800

UPDATE  laboratory SET doctor_id = doctor.doctor_id, date = '03-03-2000' FROM laboratory I
NNER JOIN doctor ON laboratory.doctor_id = doctor.doctor_id
WHERE (laboratory.amount >= 800) AND (doctor.address = 'Navasari')
  • In the above query, SQL update from select with inner join is used with the join of two tables laboratory and doctor to conditionally update  doctor_id from doctor table if doctor is living in “navsari’
  • Date column of laboratory is also updated if the total laboratory amount is more than or equal to 800 with 3rd march 2000

OUTPUT:

To view the result of the above query we need to retrieve records from laboratory table using SQL select statement

SELECT  lab_no, patient_id, doctor_id, date, amount
FROM   laboratory;

SQL UPDATE from SELECT statement [SOLVED]

 

Method-3: SQL Update from SELECT using the MERGE statement

The MERGE statement is used to synchronize the update table records with data from any select source table. The MERGE statement can be used to modify (INSERT, UPDATE, DELETE) and update target table records by referencing select source table records for the matched and unmatched rows.

Advertisement

 

Syntax

MERGE update_table_name 
USING(SELECT * FROM [select_table_name])
ON update_table_reference_column_name=select_table_reference_column_name 
WHEN MATCHED THEN UPDATE SET 
update_column1 = select_column1, update_column2 = select_column2;

MERGE statement is used for updating data in the update_table, It then references another select_table when the USING clause is applied.

The WHEN MATCHED then specifies the merge JOIN (Inner Join) between the source and target table.

 

Example-1: SQL Update single value from SELECT

Write SQL query to update ‘Navasari’ city patient’s doctor_id with doctor_id of doctor who is living in ‘Navasari’

MERGE patient as p
USING (SELECT * FROM doctor) as d
ON p.doctor_id=d.doctor_id
WHEN MATCHED AND (p.address='Navsari' AND d.address='Navsari') THEN 
UPDATE SET p.doctor_id=d.doctor_id;
  • In the above query, SQL MARGE statement is used to synchronize value of patient table doctor_id column with doctor table doctor_id data
  • If records are match with condition and reference column , the record will be updated in patient table

OUTPUT:

To view the result of above query we need to retrieve records from patient table using SQL select statement

SELECT  patient_id, name, age, gender, address, disease, doctor_id
FROM   patient;

SQL UPDATE from SELECT statement [SOLVED]

 

Example-2: SQL Update multiple values from SELECT

Write SQL query to update doctor_id and date in patient laboratory data with doctor_id of ‘Navasari’ city and date as 3rd march 2000 respectively if the billing amount is greater than or equal to 800

Advertisement
MERGE laboratory as l
USING (SELECT * FROM doctor) as d
ON l.doctor_id=d.doctor_id
WHEN MATCHED AND (amount >=800 AND d.address='Navsari') THEN 
UPDATE SET l.doctor_id=d.doctor_id,date='03-03-2000';
  • In the above query, SQL MARGE statement is used to merge two table laboratory and doctor table data based on reference column and condition
  • If matched then the value in the doctor_id column of the laboratory table will be updated and also column date value will be updated with 03-03-2000

OUTPUT:

To view the result of the above query we need to retrieve records from the laboratory table using SQL select statement

SELECT  lab_no, patient_id, doctor_id, date, amount
FROM  laboratory;

SQL UPDATE from SELECT statement [SOLVED]

 

Summary

In this article on SQL update from select, We have covered how to UPDATE from a SELECT in SQL Server, list out three methods to update from select: using SELECT SUBQUERY, INNER JOIN, and MARGE statement, also explain each method with practical examples by updating single and multiple values.

 

References

SQL UPDATE
SQL INNER JOIN

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X