UPDATE Statement in SQL

DevSniper
Published : 10 July 2024

184 Views

UPDATE is a fundamental SQL statement used to modify existing records in a database table. SQL UPDATE query allows you to change the values of one or more columns based on specified conditions. Whenever you need to update one or more records in a database table you should use SQL update query. This article provides a detailed overview of how to effectively use the UPDATE statement in SQL.

The primary purpose of the UPDATE statement is to modify data that already exists in a table. It is commonly used when you need to update one or more records with new values without deleting or inserting new rows.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE statement is used to modify existing records in a database table.
UPDATE query allows you to change the values of one or more columns based on specified conditions.
It's recommended to always use a meaningful WHERE clause with UPDATE statement to avoid unintentionally updating all rows in a table.
Before executing update query on production environment it is advised to test them in a development or staging environment to verify their correctness.
Before executing update query, especially affecting large portions of data, it is recommended to take a backup of the database to mitigate risks of data loss.
When updating critical data, consider using transactions (BEGIN TRANSACTION, COMMIT, ROLLBACK) to ensure atomicity and consistency.
UPDATE statement supports complex conditions and expressions, enabling dynamic updates based on various criteria.

Demo Database

CREATE TABLE Users (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Mobile VARCHAR(15) UNIQUE NOT NULL,
    DOB DATE,
    Weight DECIMAL(5, 2)
);
CREATE TABLE UserAddress (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    UserId INT NOT NULL,
    AddressType ENUM('Permanent', 'Temporary', 'Other') NOT NULL,
    Address VARCHAR(255) NOT NULL,
    Country VARCHAR(50),
    City VARCHAR(50),
    PostalCode VARCHAR(10)
);

Updating Specific Rows

You can use UPDATE statement to modify specific rows. For updating specific rows define a precise WHERE condition that meets your criteria.

SQL query to update postal code of Delhi city.

UPDATE UserAddress
SET PostalCode =110001
WHERE City="Delhi";

Updating Single Row

To update a single row, use the primary key or a unique column value in the WHERE clause for exact matching, ensuring that only the intended row is updated.

UPDATE Users
SET Name = "jack", Mobile ="9876543210"
WHERE Id=3;

Updating with complex conditions

If you have a complex business logic for updating table records use the UPDATE statement along with WHERE clause, inside WHERE clause define the specific conditions that meet your business logic criteria.

UPDATE UserAddress
SET PostalCode =1234
WHERE Country="India" AND State ="Maharashtra" 
AND City="Pune" AND AddressType="Permanent";