SQLite - Update Data

• Alter a Table
• Delete Data

The UPDATE statement can be used to update data in a table.

The WHERE clause can be used in order to specify exactly which rows should be updated.

You can update all rows, some rows, or none, depending on the filtering conditions applied by the WHERE clause.

A Simple Update

Here's a simple update that changes artist number 16 to Mothers of Invention:

And verify the update with a quick SELECT:

sqlite> SELECT * FROM Artists WHERE ArtistId = 16;

ArtistId              ArtistName                                          Bio       

--------------------  --------------------------------------------------  ----------

16                    Mothers of Invention                                          

Update all Rows

You can update all rows of a table simply by omitting the WHERE clause.

Let's try it:

And check it with a SELECT statement:

sqlite> SELECT ArtistName, Bio FROM Artists;	

ArtistName            Bio                                               

--------------------  --------------------------------------------------

Joe Satriani          Australian jazz band centred around polyrhythms.  

Steve Vai             Australian jazz band centred around polyrhythms.  

The Tea Party         Australian jazz band centred around polyrhythms.  

Noiseworks            Australian jazz band centred around polyrhythms.  

Wayne Jury            Australian jazz band centred around polyrhythms.  

Mr Percival           Australian jazz band centred around polyrhythms.  

Iron Maiden           Australian jazz band centred around polyrhythms.  

Atmasphere            Australian jazz band centred around polyrhythms.  

Ian Moss              Australian jazz band centred around polyrhythms.  

Magnum                Australian jazz band centred around polyrhythms.  

Strapping Young Lad   Australian jazz band centred around polyrhythms.  

Slayer                Australian jazz band centred around polyrhythms.  

Primus                Australian jazz band centred around polyrhythms.  

Pat Metheny           Australian jazz band centred around polyrhythms.  

Frank Gambale         Australian jazz band centred around polyrhythms.  

Mothers of Invention  Australian jazz band centred around polyrhythms.  

The Wiggles           Australian jazz band centred around polyrhythms.  

Oops! I don't think all these bands are Australian jazz bands centred around polyrhythms.

Not to worry, we can fix that. We can set that column to NULL for all artists that this bio doesn't apply to. In this case, the bio only applies to artist ID 8 so we'll null all rows where the ArtistId is not equal to 8.

And now the bio is only stored against the correct artist.

sqlite> SELECT ArtistName, Bio FROM Artists;

ArtistName            Bio                                               

--------------------  --------------------------------------------------

Joe Satriani                                                            

Steve Vai                                                               

The Tea Party                                                           

Noiseworks                                                              

Wayne Jury                                                              

Mr Percival                                                             

Iron Maiden                                                             

Atmasphere            Australian jazz band centred around polyrhythms.  

Ian Moss                                                                

Magnum                                                                  

Strapping Young Lad                                                     

Slayer                                                                  

Primus                                                                  

Pat Metheny                                                             

Frank Gambale                                                           

Mothers of Invention                                                    

The Wiggles                                                             

• Alter a Table
• Delete Data