Re: [sqlite] INSERTing records in two tables?
Gilles wrote:
>
> I'm an SQL newbie, and would like to know how to perform the following
> INSERT's:
>
> BEGIN;
> INSERT INTO customers (id,name) VALUES (NULL,'John Doe');
> ;How to get ROW_ID?
> INSERT INTO phones (tel,id_customers) VALUES ('1234567',ROW_ID);
> COMMIT;
>
Gilles,
In this particular case you can use last_insert_rowid() in the second
insert as Igor has suggested. (Only if the id column is declared
"ineteger primary key". If the id column is not declared that way then
the rowid and the id are not the same value. Since you are inserting a
null for the id, I assume it is declared that way.)
In the general case where you want to add multiple telephone numbers
linked to a customer record that won't work, because the next call to
last_insert_rowid() will return the rowid of the row that was inserted
into the phones table.
In general you will need to use a select after the first insert to get
the customer id you want, and save that value to be used in the
subsequent telephone number insert operations. You can do that in SQL or
in your application code.
In pseudo code it would be something like this:
in_tel = "insert into phones (tel, id_customer) values ('%s', %d)"
do_sql("begin")
do_sql("insert into customers (id,name) values (NULL,'John Doe')")
id = do_qry("select last_insert_rowid()")
sprintf(sql, in_tel, "1234567", id)
do_sql(sql)
sprintf(sql, in_tel, "9876543", id)
do_sql(sql)
do_sql("commit")
If you need to do this directly in SQL, you can store the rowid in a
temp table like this.
BEGIN;
INSERT INTO customers (id,name) VALUES (NULL,'John Doe');
create temp table cust_id as select last_insert_rowid as id;
INSERT INTO phones (tel,id_customers) VALUES ('1234567',
(select id from cust_id));
INSERT INTO phones (tel,id_customers) VALUES ('9876543',
(select id from cust_id));
drop table cust_id;
COMMIT;
You could of course also repeat the select lookup for each subsequent
telephone insert using a subselect. If you do that you need to ensure
the customer name values are unique.
insert into phones (tel, id_customer)
values ('1234567', (select id from customer where name = 'John Doe'));
insert into phones (tel, id_customer)
values ('9876543', (select id from customer where name = 'John Doe'));
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing records in two tables?
last_row_id is not a function. It is a predefined value.
Gilles wrote:
> On Sat, 9 Feb 2008 08:41:56 -0500, "Igor Tandetnik"
> <[EMAIL PROTECTED]> wrote:
>> INSERT INTO phones (tel,id_customers) VALUES ('1234567',last_row_id());
>
> Thanks, but I'm getting an error (FWIW, I'm using 3.5.4):
>
>
> sqlite> BEGIN;INSERT INTO customers (id,name) VALUES (NULL,'John
> Doe');INSERT INTO phones (tel,id_customers) VALUES
> ('1234567',last_row_id());COMMIT;
> SQL error: no such function: last_row_id
>
>
> Besides, I need to get the last ROW_ID of table "customers", not the
> one from table "phones".
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing records in two tables?
"Gilles" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Sat, 9 Feb 2008 08:41:56 -0500, "Igor Tandetnik"
> <[EMAIL PROTECTED]> wrote:
>> INSERT INTO phones (tel,id_customers) VALUES
>> ('1234567',last_row_id());
>
> Thanks, but I'm getting an error (FWIW, I'm using 3.5.4):
Sorry, it's last_insert_rowid()
http://sqlite.org/lang_expr.html
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing records in two tables?
On Sat, 9 Feb 2008 08:41:56 -0500, "Igor Tandetnik"
<[EMAIL PROTECTED]> wrote:
>INSERT INTO phones (tel,id_customers) VALUES ('1234567',last_row_id());
Thanks, but I'm getting an error (FWIW, I'm using 3.5.4):
sqlite> BEGIN;INSERT INTO customers (id,name) VALUES (NULL,'John
Doe');INSERT INTO phones (tel,id_customers) VALUES
('1234567',last_row_id());COMMIT;
SQL error: no such function: last_row_id
Besides, I need to get the last ROW_ID of table "customers", not the
one from table "phones".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERTing records in two tables?
"Gilles" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> =
> BEGIN;
> INSERT INTO customers (id,name) VALUES (NULL,'John Doe');
> ;How to get ROW_ID?
> INSERT INTO phones (tel,id_customers) VALUES ('1234567',ROW_ID);
> COMMIT;
> =
INSERT INTO phones (tel,id_customers) VALUES ('1234567',last_row_id());
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
