SQLite | Insert, Update, and Delete Data | Update Data (UPDATE)

Tags:

This article explains how to use the UPDATE statement to replace data stored in a table with new values.

Updating Data in a Table

Use the UPDATE statement to change existing data. Its syntax is as follows.

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

The WHERE clause identifies the rows to update. If several rows satisfy the condition, all of them are updated at once. Omitting WHERE updates every row in the table. The SET clause specifies the columns and their new values, and it can update several columns at once.

Create a table and insert sample data.

create table employee (id integer, name text, unit text, flag text);
insert into employee values (1, 'devkuma', 'Sales', 'Yes');
insert into employee values (2, 'kimkc', 'Office', 'No');
insert into employee values (3, 'araikuma', 'Office', 'Yes');
insert into employee values (4, 'happykuma', 'Tech', 'Yes');
insert into employee values (5, 'mykuma', 'Sales', 'No');

First, change the name value of the row whose id is 3 and verify the result.

update employee set name = 'raccoon' where id = 3;
select * from employee where id = 3;

id          name        unit        flag
----------  ----------  ----------  ----------
3           raccoon     Office      Yes

Next, update multiple rows at once by changing Office to Desk in the unit column.

update employee set unit = 'Desk' where unit = 'Office';
select * from employee;

1  devkuma    Sales  Yes
2  kimkc      Desk   No
3  raccoon    Desk   Yes
4  happykuma  Tech   Yes
5  mykuma     Sales  No

Finally, omit WHERE to change the flag value in every row.

update employee set flag = 'unknown';
select * from employee;

1  devkuma    Sales  unknown
2  kimkc      Desk   unknown
3  raccoon    Desk   unknown
4  happykuma  Tech   unknown
5  mykuma     Sales  unknown