Update query using Subquery in Sql Server

Slide into higher Gear with Indexes

Large datasets? No worries. We can accelerate queries by creating indexes on the columns in the join condition. For example, let's increase the speed for 'Name' column in the tempData and tempDataView tables:

CREATE NONCLUSTERED INDEX idx_tempData_name ON tempData(Name);
CREATE NONCLUSTERED INDEX idx_tempDataView_name ON tempDataView(Name);


Ain't no mountain high enough for the INNER JOIN to keep us from a lightning-fast update using another table's data.

The Aliases-Subqueries tag team

Aliases come in handy to declutter complex queries, especially when dealing with multiple joins or subqueries. Here's what we mean:

UPDATE t
SET t.Marks = sub.Marks
FROM tempDataView AS t
INNER JOIN (
 SELECT Name, Marks FROM tempData WHERE Name = t.Name -- "Hey, Name is the word of the day!"
) AS sub ON t.Name = sub.Name; 


Our subquery above fetches the right punches of data to update. Changing those left hooks (SELECT joins) to right uppercuts (UPDATE joins) will crack open the door to more efficient and intuitive record updating.

Squaring Off with Complex Update Scenarios

Now let's tackle the beasts: One-to-many relationships against No-match scenarios. Ready? Here's a powerful move:

UPDATE t
SET t.DetailField = COALESCE(sub.DetailValue, 'DefaultValue') -- "It's a COALEaSC(ape) route!"
FROM YourTargetTable t
LEFT JOIN (
 SELECT KeyField, DetailValue FROM SourceTable   -- "Catch 'em all!"
) sub ON t.KeyField = sub.KeyField; 


The above nails the update on target table and even sets a default value if SourceTable doesn't have any matching record. Practice that better than Ali floatin' a butterfly, and you'll be stinging' like a bee in the SQL ring!

Next Level Update Techniques

Unique keys are our bestfriend to dodge duplicates or unexpected multiplications when our subquery returns several matches. But first, let's start with some error checking:

-- Ensures our subquery is a good boy and doesn't return more than one value
SELECT KeyField, COUNT(*)
FROM SourceTable
GROUP BY KeyField
HAVING COUNT(*) > 1;  -- "Now that's some COUNT Dracula stuff!"


Run this check before your update to keep your data consistent and integrity-solid.

Master of Subqueries

Here's how you can draft SQL statements that work like a Swiss army knife:

UPDATE p
SET p.SummaryData = sub.ComputedData
FROM ParentTable p
JOIN (
 SELECT ForeignKey, SUM(DetailData) AS ComputedData  -- "Let's tally up!"
 FROM ChildTable
 GROUP BY ForeignKey -- "Conform! Group them up!"
) sub ON p.PrimaryKey = sub.ForeignKey;


This one's neat: summarizing rows that contain certain details to keep a clear, clean single source of truth within your database.

Tackling Deletion with subqueries

Remember, our techniques apply to delete records using subqueries too:

DELETE FROM TargetTable
WHERE id IN (
 SELECT id FROM SourceTable WHERE condition = 'Criteria'
);


But hey, always double-check your conditions. We wouldn't want accidental data losses now, would we?