What is SQL Update?

The SQL Update command lets us change data in a database table. It's like a tool for making updates, giving developers the ability to change certain records based on set conditions.

Syntax of SQL Update Statement?.

UPDATE table_name
SET column_name1 = value1, column_name2 = value2, ...
WHERE condition;

The UPDATE keyword initiates the process, followed by the target table_name. The SET clause allows you to specify the columns and their corresponding values, while the WHERE clause acts as the gatekeeper, determining which records undergo the transformation.

Real-world Example

Imagine you have a table of customer details, and you want to update a customer's name based on their ID:

cus_id	cus_name	amount	lane_id	cus_type
001	Customer1	45000.00	1000	WholeSale
002	Customer2	40000.00	2000	Retail
003	Customer3	27000.00	1000	WholeSale
004	Customer4	25000.00	2000	Retail
005	Customer5	20000.00	1000	Retail
006	Customer6	18000.00	1000	Retail
007	Customer7	15000.00	1000	WholeSale
008	Customer8	14000.00	2000	Retail
009	Customer9	13000.00	2000	WholeSale

UPDATE customer_details
SET cus_name = 'Mike'
WHERE cus_id = '003';

Here, the cus_name is transformed to 'Mike' specifically for the student with ID '3'. See results after the update

cus_id	cus_name	amount	lane_id	cus_type
001	Customer1	45000.00	1000	WholeSale
002	Customer2	40000.00	2000	Retail
003	Mike	27000.00	1000	WholeSale
004	Customer4	25000.00	2000	Retail
005	Customer5	20000.00	1000	Retail
006	Customer6	18000.00	1000	Retail
007	Customer7	15000.00	1000	WholeSale
008	Customer8	14000.00	2000	Retail
009	Customer9	13000.00	2000	WholeSale

Updating Multiple Fields

Efficiency meets versatility when updating multiple fields simultaneously:

UPDATE customer_details
SET cus_name = 'Anne', cus_type = 'Retail'
WHERE cus_id = '003';

See results after the update

cus_id	cus_name	amount	lane_id	cus_type
001	Customer1	45000.00	1000	WholeSale
002	Customer2	40000.00	2000	Retail
003	Anne	27000.00	1000	Retail
004	Customer4	25000.00	2000	Retail
005	Customer5	20000.00	1000	Retail
006	Customer6	18000.00	1000	Retail
007	Customer7	15000.00	1000	WholeSale
008	Customer8	14000.00	2000	Retail
009	Customer9	13000.00	2000	WholeSale

Update with Select

Experience the power of using SELECT and UPDATE together as you fine-tune your data with precision.

UPDATE tableDestination
SET tableDestination.col = value
WHERE EXISTS (
    SELECT col2.value
    FROM tblSource
    WHERE tblSource.join_col = tblDestination.Join_col
    AND tblSource.Constraint = value
);

This advanced technique allows you to update records based on conditions defined in a SELECT statement.