SQL Update With JOIN Techniques for Efficient Data Management

One of many things SQL Server, MySQL, PostgreSQL, and Oracle have in common is that they store data in multiple tables related to each other with a common key value. This means you cannot avoid retrieving records from two or more tables based on some condition while working with these relational databases. In SQL, this can be easily achieved with the help of JOINs. In this article, we are going to talk about the UPDATE JOIN operation — a powerful tool in database management that allows you to perform precise and efficient data updates, ensure data integrity, and improve overall operational performance.

Contents

• Syntax diversity in UPDATE JOIN
• Examples of the SQL Server UPDATE JOIN
• Hands-on examples
• Single table UPDATE limitation
• Maximizing efficiency in data synchronization
• Exercising caution with production data
• Try it yourself with dbForge Studio
• dbForge SQL Complete: Your ultimate add-in for SSMS
• Further learning
• Conclusion

Syntax diversity in UPDATE JOIN

Our first step in getting to know the UPDATE JOIN operation would be understanding its syntax and functions. As you might already know, there are several types of JOINs in SQL Server. Let us see how they work together with the UPDATE statement:

• INNER JOIN: Allows you to update rows in one table based on matching values in another table. This is useful for updating records where there is a direct relationship between the two tables.
• LEFT OUTER JOIN: Affects rows in the left table based on matching values in the right one while also including unmatched rows from the left table.
• RIGHT OUTER JOIN: Affects rows in the right table based on matching values in the left one while also including unmatched rows from the right table.
• SELF JOIN: Changes rows within the same table based on matching conditions.
• CROSS JOIN: Updates all combinations of rows from both tables.

The basic syntax of the query will look somewhat like this:

UPDATE table_name
SET table_name.column_name = table_name1.column_name, ...
FROM table_name
[ INNER | LEFT ] JOIN table_name1 ON join_predicate
[ WHERE Condition(s) ];

Examples of the SQL Server UPDATE JOIN

In the upcoming section of this article, let us focus on the real-life scenarios that might require using UPDATE JOIN. There is an endless list of applications, but we selected five of the most common examples:

1. Synchronizing product prices across different regions.
2. Updating employee salaries based on performance ratings.
3. Correcting mistakes.
4. Calculating profits using multiple tables.
5. Updating multiple columns from multiple tables.

Stay tuned as we address some of the mentioned scenarios using a multi-functional GUI — dbForge Studio for SQL Server — which covers a wide range of tasks related to SQL Server development, management, and administration.

Hands-on examples

As promised, we are going to address a few real-life scenarios where the UPDATE JOIN operation comes in handy. We will use a newly created TestDatabase filled with data using the Data Generator tool for demonstration purposes.

Imagine we own a restaurant and have just updated the email address of one of our employees in the database. However, we executed the UPDATE query without the WHERE clause.

UPDATE Employee
SET Email = '[email protected]';

In order to fix this unfortunate mistake, we can restore all correct emails using the audit table with the help of the UPDATE JOIN to get back the correct data.

UPDATE Employee
SET Employee.Email = EmployeeAudit.Email
FROM Employee
INNER JOIN EmployeeAudit
  ON Employee.EmployeeID = EmployeeAudit.EmployeeID;

To check the results, use the following query:

SELECT * FROM Employee;

Single table UPDATE limitation

In SQL Server, the UPDATE statement allows you to update only columns in a single table at a time, even when using JOINs. This can be an issue when dealing with complex updates involving multiple tables:

UPDATE OrderItems
SET OrderItems.BillAmount = 0
   ,Menu.Discount = 0
FROM OrderItems
INNER JOIN Menu
  ON OrderItems.MenuId = Menu.MenuId;

On executing the above query, you will get an error stating that the multi-part identifier could not be bound.

Instead of giving up, we came up with an idea on how to overcome this challenge. What we can do is handle both changes as two separate statements:

-- Step 1: Update BillAmount in OrderItems to 0 based on the corresponding MenuId in Menu
UPDATE OrderItems
SET BillAmount = 0
FROM OrderItems
INNER JOIN Menu ON OrderItems.MenuId = Menu.MenuId;

-- Step 2: Update Discount in Menu to 0 based on the corresponding MenuId in OrderItems
UPDATE Menu
SET Discount = 0
FROM Menu
INNER JOIN OrderItems ON OrderItems.MenuId = Menu.MenuId;

On executing the queries above one after another, you can check the results in the OrderItems table:

SELECT * FROM OrderItems;

… and in the Menu table:

SELECT * FROM Menu;

Maximizing efficiency in data synchronization

Using SQL Server UPDATE JOIN for data synchronization tasks can be highly efficient, especially when conditional logic based on related table data is employed. It allows you to perform precise and targeted updates, reducing the need for multiple queries, data transfers, and extra tools. In this section, let us go through a couple of examples of how this operation facilitates seamless data updates.

Direct and targeted updates

UPDATE JOIN directly updates the specific rows in your table based on related data from another one.

For instance, your employees’ contact information has just been updated and is now stored in the NewContacts table:

Using the following script, you can update the Employee table:

UPDATE e
SET e.ContactNo = nc.NewContactNo
FROM Employee e
INNER JOIN NewContacts nc ON e.EmployeeID = nc.EmployeeID;

As a result, the ContactNo column in the Employee table contains the same data as the NewContactNo column from the NewContacts table.

Conditional logic for precision

Applying good old logic is beneficial for every aspect of our lives, and working with databases is not an exception. Technology can only assist us so far. Since you are the one who has the best understanding of your database, take the time to consider the most effective and optimized methods for updating your data.

For example, if you have been shipping the clients’ orders all week long, you now have a bunch of completed orders that need to be marked as such in the system. Instead of going through each one of them or updating all in bulk, you can update only the ones that have already been delivered:

UPDATE o
SET o.OrderStatus = 'Completed'
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderId = oi.OrderId
WHERE o.ShippedDate IS NOT NULL;

On executing the query, you will have all the orders that have a delivery date marked as ‘Completed’.

Exercising caution with production data

UPDATE JOINS is a powerful tool for synchronizing and maintaining data across multiple tables. However, you need to be extra careful when updating the production database. Working with those requires careful planning to prevent data inconsistencies and ensure integrity. Besides optimizing each individual query, you can take into account some overall practices to improve your experience:

1. Index optimization: Make sure that columns used in JOINs and conditions are properly indexed.
2. Foreign key constraints: Check and enforce foreign key constraints to maintain referential integrity.
3. Batch updates: For large datasets, consider updating in batches to reduce the load on the database and avoid locking issues.
4. Cascade updates: Use cascading updates if appropriate, but be cautious, as this can propagate changes in ways that are hard to track.
5. Use transactions: Wrap updates in transactions to ensure atomicity, consistency, isolation, and durability (ACID properties).
6. Test in staging: Perform updates in the staging environment before synchronizing them with production. This helps identify potential issues without risking production data.
7. Regular maintenance: Regularly update statistics and run index defragmentation to keep the database performance optimal.
8. Logging changes: Log before-and-after values of updated rows to an audit table.

Try it yourself with dbForge Studio

Even though SQL Server Management Studio (SSMS) is the most popular and familiar tool that allows you to work with SQL Server databases, it is not the only one. Moreover, in the continuously evolving world of database development, administration, and management, new GUIs keep appearing like mushrooms after the rain. How do you choose the tool that is perfect for you among all this variety?

Let us compare dbForge Studio for SQL Server with SSMS so that you can make an informed decision on which solution best aligns with your daily requirements:

Feature	dbForge Studio for SQL Server	SQL Server Management Studio
User-friendly interface	Boasts an intuitive and user-friendly interface, providing a smooth user experience to both beginners and experienced developers.	While powerful, SSMS can have a steeper learning curve, particularly for those new to SQL Server tasks.
Advanced functionality	Offers a wide range of advanced features, including a visual query builder, data and schema comparison tools, and advanced SQL editing capabilities.	Provides the essentials but may lack some of the advanced features available in dbForge Studio.
Integrated tools	Comes with integrated tools for schema and data comparison, enabling seamless data synchronization and database management out of the box.	While offering basic tools, SSMS may require auxiliary add-ins to expand its feature set.
Data generation	Provides a customizable data generation tool that delivers realistic test data, offering flexibility in data generation for specific tables and columns.	Incorporates fundamental data generation features but may necessitate additional scripts or tools for advanced and specific data generation requirements.
Cross-platform support	Supports Windows, macOS, and Linux, providing flexibility for users on different operating systems.	Is primarily designed for Windows, limiting its accessibility for macOS users.

Take advantage of dbForge Studio for SQL Server by downloading a free fully-functional 30-day trial version and installing it on your computer. With a huge pack of advanced features and an intuitive GUI, this all-in-one tool can maximize productivity and make SQL Server database development, administration, and management process efficient. The Studio can also be of use when it comes to today’s topic, from generating test data to performing advanced UPDATE operations.

For a more visual comparison of the two solutions, watch the SSMS vs. dbForge Studio for SQL Server – Features Comparison video on the Devart YouTube channel.

dbForge SQL Complete: Your ultimate add-in for SSMS

And if you want to stick to SSMS yet its default functionality feels insufficient to you, try augmenting it with dbForge SQL Complete, an add-in that offers the following capabilities:

Intelligent Code Completion	SQL Complete provides intelligent code completion capabilities, suggesting SQL keywords, object names, and even column names as you type. It helps to save time and reduces syntax errors by offering relevant suggestions based on the context.
SQL Snippets	The tool includes a collection of code snippets for common SQL operations, such as creating tables, stored procedures, and queries. These snippets can be quickly inserted into your code, boosting productivity and reducing repetitive typing.
Code Formatting	There are powerful SQL formatting options that automatically format your SQL code according to the default or customized formatting rules. It ensures consistency and readability, making your code more manageable and professional.
Code Refactoring	You can also perform easy refactoring of your SQL code. dbForge SQL Complete provides functionality to rename objects, extract SQL code into a separate stored procedure, and perform other code refactoring tasks, helping you maintain a clean and organized codebase.
Productivity Extension	SQL Complete’s productivity features include code snippets, intelligent code formatting, quick object search, SQL code highlighting, code navigation, code refactoring, and advanced code suggestion, all of which facilitate faster and more efficient SQL development.
Code Highlighting and Analysis	The add-in provides advanced code highlighting and analysis capabilities. It identifies syntax errors, unresolved references, and potential performance issues in your SQL code, helping you catch and fix problems early in the development process.

These are just a few of the features and benefits of using SQL Complete. You can see all of them in action by downloading SQL Complete for a free 14-day trial.

Further learning

• SQL Server Tutorials
• dbForge Studio Documentation 
• dbForge Studio Video Tutorials 
• Devart Academy

Conclusion

In this article, we have been talking about the theoretical aspects of the UPDATE JOIN operation, as well as the ways to apply it in practice. We have gathered some illustrative examples of its usage and added a little bit of best-practice advice to make your experience even better. We used dbForge Studio for SQL Server as a trusted instrument for all the practical exercises. If you liked what you saw, you can give it a try yourself: download the Studio for a free 30-day trial!