SQL Update Query

How do you modify existing data in a SQL table?

The UPDATE statement in SQL is used to modify existing data within a table. It allows you to change values in specific rows based on conditions. This is a fundamental operation for maintaining and updating data in a database.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The UPDATE statement is a crucial part of any SQL developer's toolkit. It allows you to change the values of columns in one or more rows of a table. This is essential for keeping your database data current and accurate. Think of it as a way to edit information already stored in your database. You can update individual rows or multiple rows based on specific criteria. For instance, you might need to update customer addresses, change product prices, or update order statuses. The UPDATE statement is a powerful tool for data maintenance and manipulation. It's important to understand the syntax and use of WHERE clauses to target the correct rows for updates. Using a WHERE clause is crucial to avoid unintended changes to data.

Why SQL Update Query is important

The UPDATE statement is essential for maintaining accurate and up-to-date data in a database. It allows developers to modify existing records, ensuring that the information stored reflects current realities. Without UPDATE, databases would quickly become outdated and unreliable.

Example Usage


-- Create a trigger to automatically update the 'TotalValue' column in the 'Orders' table
-- when a new order is inserted.
CREATE TRIGGER trg_UpdateTotalValue
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
    UPDATE Products
    SET StockQuantity = StockQuantity - NEW.Quantity
    WHERE ProductID = NEW.ProductID;
    UPDATE Orders
    SET TotalValue = NEW.Quantity * NEW.UnitPrice
    WHERE OrderID = NEW.OrderID;
END;

-- Example of an INSERT statement that will trigger the above trigger
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, UnitPrice)
VALUES (101, 10, 20, 5, 10.00);

-- Check the updated values in the Orders and Products tables
SELECT * FROM Orders WHERE OrderID = 101;
SELECT * FROM Products WHERE ProductID = 20;

Common Mistakes

Forgetting to specify the `WHERE` clause, leading to unintended updates of all rows in the table.
Using incorrect data types in the `SET` clause, resulting in errors.
Using incorrect column names in the `SET` clause.
Misspelling column names or table names.

SQL Update Query

How do you modify existing data in a SQL table?

Description

Why SQL Update Query is important

Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL