SQLite3 Writing to specific records

[Road Kill Kenny]

I have a question for those of you who use sqlite3.

 

I know how to read and write from and into a database. However, I'm not sure exactly how to write into a specific record of a table that has already been created.

 

So for example:

 

Table T1 has Columns C1 & C2

 

so I would prepare my statement like so:

 

sqlite3_stmt *statement;

sqlite3_prepare_v2(database, "INSERT OR REPLACE INTO T1 (C1,C2) VALUES(10,50)" , -1, &statement, 0)

sqlite3_step(statement);

 

The thing I don't get is that you can choose the table name and columns but not the rows. How do you get a specific row without overwriting all the rows

 

Now I'd imagine if I used the step function here to step through the rows it would just over write everything on its way to get to the correct row.. So basically how do I just write into the row I want...

 

I'm probably just being a n00b here and missing something simple but please enlighten me

[Paul Thomas]

I think you're looking for is "last insert id" and for CPP I believe its:

 

sqlite3_last_insert_rowid(DATABASE)

 

That's for when you just insert and you want the row created for the insert. Otherwise, you would use the integer primary key as identification, or the shortcut "rowid".

[Canardia]

Use the SQL command: UPDATE ( http://www.sqlite.org/lang_update.html ). You need of course also the first column indexed, but that's what every table should have anyway to get maximum speed out of the database. Additionally you can also add the LIMIT statement, if you don't want to update all matches.

[Furbolg]

If i understand you right then use the WHERE clause. Where dont work on INSERT just on SELECT, UPDATE and DELETE but it may helps you.

 

http://www.w3schools.com/sql/sql_where.asp

[Rick]

The reason you are getting multiple different answers is because it seems that's a sqlite specific command and most other SQL engines don't have that exact command.

 

I've never used that specific syntax but from what I'm reading you must have a primary key on 1 or more columns. Then when you run that command if a "primary key violation" occurs it will do an update instead of an insert. A "primary key violation" would occur when you try to insert a record where the values are exactly the same in the columns you defined as the primary key.

 

In SQLite it looks like you would create an index and during creation there should be a setting for not allowing duplicate values for the column on a table you select.

[Road Kill Kenny]

Thanks I think UPDATE with the WHERE clause is what I'm after. Cheers should be all good now.

 

Wow its a sign I just flicked over to another tab in the chrome browser and looked like I had already clicked on the update stuff.. cheers yall.

http://www.sqlite.org/lang_update.html

 

So does this look right: (Just following the flow chart on the above linked site.

 

UPDATE T1 SET C1=1, C2=2 WHERE id=2

 

So that should update columns C1 and C2 on row 2 (id is primary key) of table T1 to be 1 and 2?

[Furbolg]

I dont know SQLite but if it uses correct SQL then yes.