SQL
Update Records Using Data from Another Table
Efficiently update existing records in one SQL table by correlating them with data from another related table using an INNER JOIN, ensuring data consistency.
UPDATE Orders
SET TotalAmount = o.Quantity * p.Price
FROM Orders o
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.Status = 'Pending';
How it works: This snippet illustrates how to update records in one table (Orders) by referencing data from another related table (Products). It uses an INNER JOIN to match orders with their corresponding product information, allowing the TotalAmount to be calculated dynamically based on the product's price. The WHERE clause further filters the updates to only 'Pending' orders, ensuring precise data manipulation.