MSSQL Concepts I

1. UPDATE query usage with JOINS

USE: When table A is updated based on values and conditions of another table B.

How it works:

UPDATE target_table
SET target_table.column = soource_table.column
FROM target_table
JOIN source_table ON target_table.id = source_table.id
WHERE condition_s;

Key points:

• Only the table after UPDATE is getting modified. (here its target_table)
• No other table in the join gets modified.
• You can use any type of join (INNER, LEFT, RIGHT)

Real World Use Cases:

1. Updating prices from a staging table ( When importing nnew pricing data, you need to update existing products:
2. Calculating aggregates from Child Records (Updating order totals based on update items):
3. Copying data between related tables (Updating Customer status based on their recent activity):
4. Updating based on multiple table conditions (Setting employee salaries based on department & performance):
5. Updating with LEFT JOIN — Handling NULLs (Marking products as discontinued if they have no recent sales):
6. Data Cleanup / Normalization (Updating denormalized data to match the source of truth):

Important Considerations:

PERFORMANCE:

• Always ensure proper indexes on join columns.
• Use WHERE clause to limit rows updated.
• Consider Batch updates for large tables

FOLLOW _ UP QUESTIONS : What is batch update ?

SAFETY:

• Test with SELECT first to verify which rows will be affected.
• Use transactions for critical updates.
• Be careful with LEFT JOINS setting NULL values.

ALTERNATIVES ( upcoming)