Databases with SQLite

Updating

Well, one of the first things we need to do is fix that subject Educatiom.

Simple enough: 

update subjects set subject="Education" where titleid=1;



sqlite> select * from subjects;
┌────┬──────────┬─────────┬───────────┐
│ id │ authorID │ titleID │  subject  │
├────┼──────────┼─────────┼───────────┤
│ 1  │ 1        │ 1       │ Education │
│ 2  │ 1        │ 1       │ Education │
│ 3  │ 1        │ 1       │ Education │
└────┴──────────┴─────────┴───────────┘

Woops! That's not what we wanted.

Computers do what they are told, not what we want.

We told it to use titleID and it did. But all records in the table have a titleid of 1.

When we created the subjects table we made a field 'id' set to 'autoincrement'. Now here's why.

update subjects set subject="Seniors" where id=2;
update subjects set subject="Computer Technology" where id=3;

Now it should be correct: 

┌────┬──────────┬─────────┬─────────────────────┐
│ id │ authorID │ titleID │       subject       │
├────┼──────────┼─────────┼─────────────────────┤
│ 1  │ 1        │ 1       │ Education           │
│ 2  │ 1        │ 1       │ Seniors             │
│ 3  │ 1        │ 1       │ Computer Technology │
└────┴──────────┴─────────┴─────────────────────┘

Use the id Primary Key field where it's available (put one in every table) to access that particular record.

replace

I've been guilty of various typos when building my databases, and luckily SQLite has a simple command to fix them.

update books set notes=replace(notes,'<br/>','') where titleid in (1033,1034,1037,1040,1046,1047);

books is the table
notes is the column I want to work on
titleid is the list of records I want to change

Here I've left an HTML tag in some text I copied into a database field for a number of records. To get rid of it, (replace it with nothing) the above command did the job.

Returning