On 20 Feb 2019, at 11:14am, Rocky Ji <rockyji3...@gmail.com> wrote: > create view summary > as > select > a.a_attribute, > b.b_attribute, > c.c_attribute > from > m2mAlphaBeta m > inner join Alpha a on a.a_id = m.FK_a_id > inner join Beta b on b.b_id = m.FK_b_id > inner join Charlie c on c.FK_C_a_id = a.a_id; > > And assuming all incoming data (say from CSV read) is correct. How do I write > a record to `summary`?
First, congratulations on getting "integer primary key autoincrement" right for keys which are FOREIGN KEY keys. Common mistake. However, I suspect you've missed a bet. Consider > create table Alpha ( > a_id integer primary key autoincrement, > a_attribute text > ); Would "a_attribute TEXT UNIQUE" be better ? This would guard against two entries in Alpha having the same attribute, which I think you wouldn't don't want. You can do the same thing for the other _attribute columns. In answer to the question, "summary" is a VIEW. You don't have to write anything to it. It looks like it already does what you want. > Like how do I get ROWID of the "just inserted" record in A to insert into C > properly. I am thinking "should I make transactions, but we don't have > variables"... and going round-and-round without solution. Ah, I see. Okay, I'm going to ignore Charlie for clarity. You have a CSV file containing two columns: a.a_attribute and b.b_attribute, and you're wondering how to get the data into Alpha, Beta and m2mAlphaBeta. The answer is ... not in one command. It can't be done. Reading two values from your CSV file, theAAttrib and theBAttrib, First, make sure that Alpha and Beta have the rows they need. INSERT OR IGNORE INTO Alpha (a_attribute) VALUES (theAAttrib); INSERT OR IGNORE INTO Beta (b_attribute) VALUES (theBAttrib); But you don't know whether those created new rows or not, so you have to find the rows you're going to want to use: newA_id = result of "SELECT a_id FROM Alpha WHERE a_attribute = '< theAAttrib>'" newB_id = result of "SELECT b_id FROM BETA WHERE b_attribute = < theBAttrib>" Then you have the values you need for "INSERT INTO Charlie". Unforunately, you cannot use last_insert_rowid() because you do not know whether your "INSERT OR IGNORE" inserted a new row or not. Fortunately, your "UNIQUE" constraints on the attribute columns means that you have indexes on them. This means that the "SELECT" commands will execute very quickly. Extend the above for Charlie. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
