2 Top SQL Queries to Update Table: Subquery and Join

Subqueries and joins are the primary techniques used in SQL to update Tables. These queries help you understand that you can update single or multiple tables. If you go into details, the purpose of the set operator is to assign a new value for update.

Here’s an SQL query that uses a subquery to update a table. Subqueries allow you to use multiple tables in your queries.

Table of Contents

1. Subquery Method
2. JOIN Method
3. Ensure these before you update a table
4. Conclusion

Subquery Method

To update a table using a subquery, follow these steps. Begin by writing the subquery to select the data you want from the second table. For example, if your tables are EMP1 and EMP2, you can write the subquery as:

UPDATE EMP1 
SET SAL = 100000 
WHERE EMP_NO IN (SELECT EMP_NO FROM EMP2 WHERE DEPT_NO IS NULL);

Here, the subquery retrieves the records from the EMP2 Table for the matching emp_no, and dept_no is NULL and updates the EMP1 table with the modified salary.

JOIN Method

To update a table using a JOIN in an SQL query, follow these steps:

1. Identify the tables involved in the JOIN operation. For example, let’s consider two tables named EMP1 and EMP2.
2.  Determine the columns you want to update and their corresponding values.
3.  Use the UPDATE statement with the JOIN clause and the SET keyword to specify the columns and their new values. Here’s an example:

UPDATE EMP1
JOIN EMP2
  ON EMP1.EMP_NO = EMP2.EMP_NO
SET EMP1.SAL = EMP2.NEW_SAL
WHERE EMP2.DEPARTMENT = 'IT';

In this example, we update the SAL column in the EMP1 table by matching the EMP_NO column in EMP1 with the EMP_NO column in EMP2 using the JOIN condition. The NEW_SAL column in EMP2 holds the new salary values. We are updating the rows where the DEPARTMENT column in EMP2 is ‘IT’.

Remember to customize the table names, column names, join conditions, and filter criteria to match your specific scenario.

Using JOIN in an UPDATE query allows you to update a table based on the data from another table, providing flexibility and powerful data manipulation capabilities.

EMP1 Table

EMP_NO	SAL
1001	90000
1002	85000

EMP2 Table

EMP_NO	DEPT_ NO
1001	IT
1002	(NULL)

Ensure these before you update a table

Update data in an SQL database using an UPDATE query, here are some new points you should know:

Specify the table name

In the UPDATE query, you need to provide the name of the table you want to update.

Set the column values

Use the SET keyword followed by the column names and their new values. Separate each column and value pair with a comma.

Add conditions

You can specify conditions using the WHERE clause to update only specific rows that meet certain criteria. Based on the conditions, you can update a single row or multiple rows.

Single table update

The UPDATE SQL query is used to update data in a single table. If you need to update data across multiple tables, you may need to use more complex queries like JOIN statements.

Be cautious with conditions

When specifying conditions in the WHERE clause, ensure they are accurate to update the desired rows. Incorrect conditions may lead to unintended updates.

Backup the database

Before performing any updates, it’s always a good practice to backup your database to avoid accidental data loss.

Remember to review your query and double-check your conditions before executing the UPDATE statement to ensure you update the correct rows with the intended values.

Conclusion

• The update-SQL query is a powerful technique that enables complex and dynamic data manipulation. By utilizing a subquery in the WHERE clause, we can selectively update selective rows in the target table based on criteria from another table.
• We can update multiple tables and perform more advanced data transformations using subqueries to meet exact needs.