On 8/21/08, alp <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> I have to tables(TBL_OBJECTS, TBL_TAGS) and I want to use the ROWID of the
> last inserted row from table TBL_OBJECTS to insert a new row in TBL_TAGS
> table with the ID_OBJECT foreign key set to the ROWID value from the
> precedent table.
>
> This is the tables definition:
>
> CREATE TABLE TBL_OBJECTS (
> ID integer PRIMARY KEY NOT NULL,
> DATA text,
> PATH text
> );
>
> CREATE TABLE TBL_TAGS (
> ID integer PRIMARY KEY NOT NULL,
> ID_TAG_TYPE integer NOT NULL,
> ID_OBJECT integer NOT NULL,
> TAG_DATA text NOT NULL
> );
>
>
> My solution is:
>
> INSERT INTO TBL_OBJECTS
> (DATA,
> PATH)
> VALUES ('val1', 'val2');
>
> INSERT INTO TBL_TAGS
> (ID_TAG_TYPE,
> ID_OBJECT,
> TAG_DATA)
> VALUES (1, 2, 'a'); --doesn't count what value is inserted for ID_OBJECT
> as it will be changed in the next statement
>
> UPDATE TBL_TAGS SET ID_OBJECT = (SELECT MAX(ROWID) FROM TBL_OBJECTS)
> WHERE ROWID = (SELECT MAX(ROWID) FROM TBL_TAGS);
>
> but I am sure there is a less complex one that you can point out to me.
How about
INSERT INTO TBL_TAGS (ID_TAG_TYPE, TAG_DATA, ID_OBJECT)
VALUES (1, 'a', SELECT Max(ID) FROM TBL_OBJECTS)
>
> --
> View this message in context:
> http://www.nabble.com/How-to-use-the-ROWID-of-the-last-inserted-row-for-FK-insert-into-other-tables--tp19085514p19085514.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
