This tutorial will explain how to update data from a table in SQL and how to use them with the examples.
This is the general statement to delete rows from a table
UPDATE
tablename
SET
column1 = 'Test',
column2 = 'Test',
WHERE
condition;
Lets create a sample table and add some rows
CREATE TABLE SampleContacts (
FullName VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Age INT NOT NULL,
DiscountRate INT NULL,
UsedDiscountAmount INT NULL
);
CREATE TABLE SampleDiscounts (
Age INT NOT NULL,
DiscountRate INT NULL
);
INSERT INTO SampleContacts
(FullName, Gender, Age)
VALUES
('Contact1', 'M', 26),
('Contact2', 'M', 27),
('Contact3', 'F', 28),
('Contact4', 'F', 29),
('Contact5', 'M', 29),
('Contact6', 'M', 21),
('Contact7', 'F', 31),
('Contact8', 'F', 33),
('Contact9', 'F', 36),
('Contact10', 'M', 35),
('Contact11', 'F', 22),
('Contact12', 'M', 41),
('Contact13', 'M', 42)
INSERT INTO SampleDiscounts
(Age, DiscountRate)
VALUES
(28, 15),
(27, 20),
(26, 25)
1) Update all rows from the table
The following UPDATE statement will update DiscountRate value to 10 on all the rows from SampleContacts table
UPDATE SampleContacts SET DiscountRate=10
2) Update multiple columns from the table
The following UPDATE statement will update DiscountRate value to 10 and UsedDiscountAmount to 0 on all the rows from SampleContacts table
UPDATE SampleContacts SET DiscountRate=10, UsedDiscountAmount=0
3) Update columns from the table with conditions
The following UPDATE statement will update DiscountRate value to 20 on the rows where Age value is less than 25 from SampleContacts table
UPDATE SampleContacts SET DiscountRate=20 WHERE Age < 25
4) Update rows from related table
The following UPDATE statement will update DiscountRate value in the SampleContacts table from relevant records in SampleDiscounts table.
For the records where age value is 28 DiscountRate value will be updated as 15
For the records where age value is 27 DiscountRate value will be updated as 20
For the records where age value is 26 DiscountRate value will be updated as 25
UPDATE
sc
SET
sc.DiscountRate = sd.DiscountRate
FROM
SampleContacts sc
INNER JOIN
SampleDiscounts sd
ON sc.Age = sd.Age
Above Update statement will not update the records where there is no match on SampleDiscounts table. To use a default value for those records we can use LEFT JOIN as below. As you can see from Case statement if the value is NULL (no match) 13 will be used to update DiscountRate
UPDATE
sc
SET
sc.DiscountRate = (CASE
WHEN sd.DiscountRate IS NULL THEN 13
ELSE sd.DiscountRate
END)
FROM
SampleContacts sc
LEFT JOIN
SampleDiscounts sd
ON sc.Age = sd.Age