UPDATE STATEMENT

To update existing data in a table, you UPDATE statement is used. It requires table name, column and new value and an optional where clause if you are modifying only subset of values. Update supports subquery based updates, to limit the number of rows. You can use join or subquery to get to exact rows which needs to be updated.

UPDATE TABLE_NAME SET Column1 = newvalue1 , Column2 = new_value2 [ WHERE search_condition ];

Update Example

DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
Username Text Primary Key
, FirstName Text
, LastName Text
, Email Text
);
 

INSERT INTO Users(Username, FirstName, LastName, Email) values ('johnd','John','Dekota', '');
INSERT INTO Users(Username, FirstName, LastName, Email) values ('maryd','Mary','Denware', '');
INSERT INTO Users(Username, FirstName, LastName, Email) values ('scotth','Scott','Hamilton','');

SELECT * FROM Users;

Here we will update one user johnd with an email address.

UPDATE Users SET 
Email = 'johnd@example.com'
WHERE Username = 'johnd';

SELECT * FROM Users;

Subquery and Limit

We can also limit the number of rows which should be updated or use subquery in update statement.

UPDATE Users SET 
Email = 'maryd@example.com'
WHERE Username = ( SELECT Username FROM Users WHERE Username = 'maryd' LIMIT 1 );

SELECT * FROM Users;

Self Reference

An update statement can reference the columns it self which needs to be updated.

UPDATE Users SET 
Email = 'johnNewEmail@example.com'
WHERE Email = 'johnd@example.com';

SELECT * FROM Users;