StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInsert record then update another table
text
Body
copied!<p>I'm trying to wrap my head around how to efficiently insert data into a table that doesn't have all the information beforehand.</p> <p>I have two tables, <code>Table1</code> and <code>Table2</code>. <code>Table2</code>'s primary key is an autoincrementing int. This int serves as the ID for the row, and is also in a column in <code>Table1</code> (not unique, so multiple records in <code>Table1</code> can have the same <code>Table2.ID</code>).The problem is, how do I insert a new record into <code>Table1</code> that automatically fills in the relevant <code>Table2.ID</code> value?</p> <p>My logic is:</p> <ol> <li>Search <code>Table2</code> to see if the record exists. </li> <li>If it exists, return the ID value and insert new record in Table1 with that ID value</li> <li>If it doesn't exist, insert new record in Table2 and return the ID</li> <li>Insert new ID into relevant Table1 column</li> </ol> <p>Is it possible to have SQLite automate the insertion of the relevant ID rather than me having to do that in code (reading around, I've come across Triggers but not sure how to use them or if they're useful here)?</p> <p><strong>Updated question with example</strong> (I'm not allowed to discuss aspects of the actual game I'm working on, but this made-up example is of a similar nature)</p> <p>Table 2 will be called <code>Genre</code> and Table 1 will be called <code>Film</code> and all tables are empty. The Genre PK (auto int) will be linked to a Genre column (int) in <code>Film</code> table. The <code>Film</code> table has no <code>varchar</code> column to store the text version of the genre, just the int column.</p> <p>I insert a new <code>Film</code> record and pass in <code>Horror</code> as the genre. However, at this time, there are no <code>Horror</code> records in the <code>Genre</code> table, so there is no int value that can be inserted into the genre column of the <code>Film</code> table.</p> <p>Therefore, from my view (which may be inefficient as I'm not well versed with SQL), I need to insert the <code>Horror</code> genre into the <code>Genre</code> table first, somehow get the newly created ID, and then insert that ID into the <code>genre</code> column of the newly created <code>Film</code> record. When I add a second <code>Horror</code> film, the ID already exists in the <code>Genre</code> table, so SQLite should take that ID and place it in the new <code>Film</code> record for me. Is that possible?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload