> If I understand the question, and there is no key other than the
> auto-incrementing
> integer, there might not be a good way. It sounds like the database's design
> may
> have painted you into a corner.
Hi James,
Well, after inserting one row into table A which looks like (without specifying
the id
and letting it auto generate):
CREATE TABLE table_a (
val VARCHAR COLLATE "nocase" NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY ( id )
);
(forgive that odd looking format, its SQLAlchemy output...)
I have for example 20 rows in table B to insert referencing the above:
CREATE TABLE table_b (
val VARCHAR COLLATE "nocase",
key VARCHAR COLLATE "nocase" NOT NULL,
id INTEGER,
seqno INTEGER NOT NULL,
PRIMARY KEY ( seqno ),
FOREIGN KEY ( id ) REFERENCES table_a ( id )
);
So selecting last_insert_rowid() always gives me the 'id' of the previous row
from table_a
after an insert. So I would insert into table_a, get that rowid, and build the
remaining 20
inserts. For the sake of keeping the entire sql statement manageable, I was
hoping not to
build the next 20 statements based on SELECT id FROM table_a WHERE val='xxx' as
that string
will be very long.
So this works for one insert:
INSERT INTO table_a (val) VALUES ('xxxxxx');
INSERT INTO table_b (id, key, val)
SELECT last_insert_rowid(), 'yyy', 'zzz';
Just not sure how to perform 20 or 30 of those inserts into table_b after the
one into table_a
yields the id value I need.
Thanks!
jlc
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
