Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread P Kishor 
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 (
>   IDinteger 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_OBJECTinteger 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
>  sqlite-users@sqlite.org
>  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread alp 



Igor Tandetnik wrote:
> 
> "alp" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> 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.
>>
>> 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
> 
> Why not just
> 
> INSERT INTO TBL_TAGS
>   (ID_TAG_TYPE,
>   ID_OBJECT,
>   TAG_DATA)
>   VALUES (1, last_insert_rowid(), 'a');
> 
> See http://sqlite.org/lang_corefunc.html
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Thanks a lot Igor, didn't knew about the existence of last_insert_rowid()
function.
-- 
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--tp19085514p19087337.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread Igor Tandetnik 
"alp" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> 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.
>
> 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

Why not just

INSERT INTO TBL_TAGS
  (ID_TAG_TYPE,
  ID_OBJECT,
  TAG_DATA)
  VALUES (1, last_insert_rowid(), 'a');

See http://sqlite.org/lang_corefunc.html

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread Francis GAYREL 
Why two tables? Define a unique table and  redefine your tables as views.

alp a écrit :
> 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 (
>   IDinteger 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_OBJECTinteger 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.
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use the ROWID of the last inserted row for FK insert into other tables?

2008-08-21 Thread alp 

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 (
  IDinteger 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_OBJECTinteger 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.
-- 
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users