How to selectively replace sqlite row?

Question

In my case 2 I am trying to replace the later sqlite entry for the qrcode. I want to update the row with a new FBid. How do I select it and then replace it? Is it something to do with sqlite? or is there some logic I need to apply within my app?

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *path = [docsPath stringByAppendingPathComponent:@"easyKTDB.sqlite"];

FMDatabase *database = [FMDatabase databaseWithPath:path];

[database open];

FMResultSet *results = [database executeQuery:@"select * from ktcode where code = ?;", qrCode];

while ([results next]) {
    ktcode *aktcode = [ktcode new];
    aktcode.ktCODE = [results stringForColumn:@"code"];
    aktcode.ktFBID = [results stringForColumn:@"fbid"];
    aktcode.uniqueID = [results intForColumn:@"id"];

    [ktcodeArray addObject:aktcode];

}

switch (ktcodeArray.count)
{
    case 0 :
        [database executeUpdate:@"insert into ktcode (code, fbid) values (?, ?)", qrCode, FBid];
        NSLog(@"%@ is now registered to %@ - %@", qrCode, userName, FBid);
        break;

    case 1:
        [database executeUpdate:@"insert into ktcode (code, fbid) values (?, ?)", qrCode, FBid];
        NSLog(@"%@ now belongs to %@ - %@", qrCode, userName, FBid);
        break;

    case 2:
        //[database executeUpdate:@""];
         NSLog(@"ktCodeArray: %@", ktcodeArray);
         NSLog(@"%@ has been transfered to %@ - %@", qrCode, userName, FBid);
        break;

    default :
        NSLog(@"Nothing else can be done");
        //delete multiple entries here

You're basically asking "How do I perform UPDATE in SQLite?" — Hot Licks, Oct 09 '14 at 20:24
(Get the sqlite3 command-line tool and play with it from a command window. You can do all operations on a database from the command line and see how they work. Use that in conjunction with a basic SQLite tutorial.) — Hot Licks, Oct 09 '14 at 20:26
Yes, but how do I perform it selectively? I will always have a maximum of two entries with an identical qrcode but two different fbids. The original entry will remain untouched. But the second entry will be constantly updated with a new fbid. So Im wondering how do I select the newer entry for update? — Jim Chen, Oct 09 '14 at 20:26
Read the excellent [SQLite documentation](http://www.sqlite.org/docs.html) (or the tutorial of your choice), in particular the WHERE clause. — Hot Licks, Oct 09 '14 at 20:28

markshiz · Accepted Answer · 2014-10-09T20:34:21.720

0

It seems you are looking for the REPLACE keyword in SQLite: https://www.sqlite.org/lang_replace.html

If you have the unique identifier, then you can do:

[database executeUpdate:@"replace into ktcode (id, code, fbid) values (?, ?, ?)", uniqueId, qrCode, FBid];

edited Oct 09 '14 at 20:34

answered Oct 09 '14 at 20:28

markshiz

2,501
22
28

thank you! I know I will have to use the replace key, but I need to make sure I don't replace both entries, so I will need a way to select the later entry, Im thinking, and I might be wrong, I'll need to include a where key, for example, replace into ktcode (fbid) values (?) where fbid != ?;", FBid) – Jim Chen Oct 09 '14 at 20:36
as long as the key for your table is `id`, the other item will not be replaced. if the `id` isn't a key, then you can select and use `rowid` instead https://www.sqlite.org/lang_createtable.html#rowid – markshiz Oct 09 '14 at 20:46
corrent me if im wrong, but youre saying i need to replace where the id is the later one? – Jim Chen Oct 09 '14 at 20:49
@JimChen correct - you need to use the `id` of the item that's already in the table. – markshiz Oct 09 '14 at 20:53
ok perfect, im almost there, how do I select that id then? I have retrieved all rows with matching qrcodes, so how to I get the id from that? – Jim Chen Oct 09 '14 at 21:07
ok, so i figure out what that id is and il set it as the where value? – Jim Chen Oct 09 '14 at 21:18

How to selectively replace sqlite row?

1 Answers1