SQLite: possible to update row or insert if it doesn't exist?

Question

I'm sure I can check if a row exists by selecting it but I'm wondering if there's a slicker way that I'm just not aware of -- seems like a common enough task that there might be. This SQLite table looks something like this:

rowID  QID    ANID  value
------ ------ ----- ------
0      axo    1     45
1      axo    2     12

If the combination of QID and ANID already exists, that value should be updated, if the combination of QID and ANID doesn't already exist then it should be inserted. While its simple enough to write:

SELECT * where QID = 'axo' and ANID = 3;

And check if the row exists then branch and either insert/update I can't help but look for a better way. Thanks in advance!

Answer 1

you can use the REPLACE command. Docs

Answer 2

The insert documentation details a REPLACE option

INERT OR REPLACE INTO tabname (QID,ANID,value) VALUES ('axo',3,45)

The "INSERT OR REPLACE" can abbreviated to REPLACE.

Example in Perl

Revised the following example to utilize a composite primary key

use strict;
use DBI;

### Connect to the database via DBI
my $dbfile = "simple.db";
unlink $dbfile;
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile");

### Create a table
$dbh->do("CREATE TABLE tblData (qid TEXT, anid INTEGER, value INTEGER, PRIMARY KEY(qid, anid))");

### Add some data
my $insert = $dbh->prepare("INSERT INTO tblData (qid,anid,value) VALUES (?,?,?)");

$insert->execute('axo', 1, 45);
$insert->execute('axo', 2, 12);

$insert->finish;

### Update data
my $insert_update = $dbh->prepare("REPLACE INTO tblData (qid,anid,value) VALUES (?,?,?)");

$insert_update->execute('axo', 2, 500);
$insert_update->execute('axo', 10, 500);

$insert_update->finish;

### Print out the data
my $select = $dbh->prepare("SELECT * FROM tblData ORDER BY 1,2");
$select->execute;

while (my @row = $select->fetchrow_array()) {
    printf "Row: %s\n", join(" - ", @row);
}

$select->finish;

$dbh->disconnect;
exit 0;

Produces the following output, demonstrating the update of one row and the insert of another

Row: axo - 1 - 45
Row: axo - 2 - 500
Row: axo - 10 - 500

Answer 3

In this particular situation it turns out that there's no easy solution - at least not that I've been able to find, despite the efforts of Mark to suggest one.

The solution I've ended up using might provide useful to someone else so I'm posting it here.

I've defined a multi-column primary key as QID + ANID and attempt to insert into the table. If that particular combination exists, it will produce an error code of 23000 which I can then use an an indicator that it should be an UPDATE instead. Here's the basic gist (in PHP):

try {
  $DB->exec("INSERT INTO tblData (QID,ANID,value) VALUES ('xxx','x',1)");
}
catch(PDOException $e) {
  if($e->getCode() == 23000) {
    $DB->exec("UPDATE tblData SET value=value+1 WHERE ANID='x' AND QID='xxx'");
  }
}

The actual code I've used is a bit more complex using prepare/placeholders and handling the error if the code isn't 23000, etc.