CODEWITHSUNDEEP
sqlite
Will
Will

Reputation: 5580

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

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!

Upvotes: 12

Views: 14818

Answers (4)

Mark O'Connor
Mark O'Connor

Reputation: 77951

The insert documentation details a REPLACE option

INSERT 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

Upvotes: 10

Dale
Dale

Reputation: 544

Beware: REPLACE doesn't really equal 'UPDATE OR INSERT'...REPLACE replaces the entire row. Therefore if you don't specify values for EVERY column, you'll replace the un-specified columns with NULL or the default values.

In a simple example such as above, it's likely fine, but if you get into the habit of using REPLACE as 'UPDATE OR INSERT' you'll nuke data when you forget to specify a value for every field...just a warning from experience.

Upvotes: 15

Will
Will

Reputation: 5580

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.

Upvotes: 1

GSto
GSto

Reputation: 42350

you can use the REPLACE command. Docs

Upvotes: 4

Related Questions