Road Kill Kenny Posted February 7, 2012 Share Posted February 7, 2012 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 Quote 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 More sharing options...
Paul Thomas Posted February 7, 2012 Share Posted February 7, 2012 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". Quote Link to comment Share on other sites More sharing options...
Canardia Posted February 7, 2012 Share Posted February 7, 2012 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. Quote ■ Ryzen 9 ■ RX 6800M ■ 16GB ■ XF8 ■ Windows 11 ■ ■ Ultra ■ LE 2.5 ■ 3DWS 5.6 ■ Reaper ■ C/C++ ■ C# ■ Fortran 2008 ■ Story ■ ■ Homepage: https://canardia.com ■ Link to comment Share on other sites More sharing options...
Furbolg Posted February 7, 2012 Share Posted February 7, 2012 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 Quote Link to comment Share on other sites More sharing options...
Rick Posted February 7, 2012 Share Posted February 7, 2012 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. Quote Link to comment Share on other sites More sharing options...
Road Kill Kenny Posted February 7, 2012 Author Share Posted February 7, 2012 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? Quote 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 More sharing options...
Furbolg Posted February 7, 2012 Share Posted February 7, 2012 I dont know SQLite but if it uses correct SQL then yes. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.