Thursday, February 7, 2008

SQLite - Updating Tables

What if I need to make changes to the data in my table? As an example, I forgot to insert Armourette's birthday and Johan is not active yet. This mean I will have to run an update query on the table.

SQLite query:

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .schema person
CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
);
sqlite> select * from person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N
2|Armourette|Strydom|Armourette|2008-01-17|Mrs|Y
sqlite> UPDATE person SET ACTIVE='Y' WHERE ID=1;
sqlite> UPDATE person SET BIRTHDATE='1983-07-05' WHERE ID=2;
sqlite> select * from person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|Y
2|Armourette|Strydom|Armourette|1983-07-05|Mrs|Y
sqlite>
Using the primary key ID I am able to quickly update the correct data.