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
