Jump to content

SQLite3 Writing to specific records


Road Kill Kenny
 Share

Recommended Posts

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

STS - Scarlet Thread Studios

AKA: Engineer Ken

 

Fact: Game Development is hard... very bloody hard.. If you are not prepared to accept that.. Please give up now!

Link to comment
Share on other sites

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.

Ryzen 9 RX 6800M ■ 16GB XF8 Windows 11 ■
Ultra ■ LE 2.53DWS 5.6  Reaper ■ C/C++ C# ■ Fortran 2008 ■ Story ■
■ Homepage: https://canardia.com ■

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

STS - Scarlet Thread Studios

AKA: Engineer Ken

 

Fact: Game Development is hard... very bloody hard.. If you are not prepared to accept that.. Please give up now!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...