[Hsqldb-developers] Problem with insert into ... select ...

[<loi...@bn...>]

Hello,
here is the problem I'm having with HSQLDB (1.7.2 alpha T):

I have three tables:

1/ one describing a set of person:

create table person (
id integer primary key, -- internal code
name varchar(20)        -- external code
)

2/ one that contains a subset of persons with more informations:

create table external (
name varchar(20), -- external code
birthday date
)

3/ and the last is the same but with internal codes:

create table internal (
name integer, -- internal code
birthday date
)

giving the data:
insert into person values (1,'jhon');
insert into person values (2,'jim');
insert into person values (3,'bob');

and:
insert into external values ('bob',now);
insert into external values ('jhon',now);
insert into external values ('jim',now);
insert into external values ('bob',now);
insert into external values ('julia',now);


I would like to convert the data in the external table into the internal ta=
ble:
i.e. expected result in table internal:

 name | birthday
 3    | 12-02-2003
 1    | 12-02-2003
 2    | 12-02-2003
 3    | 12-02-2003
null  | 12-02-2003

So I thought the following statement would do it:
1/ insert into internal (name,birthday) (select (select p.id from person p =
where p.name=3Dext.name), ext.birthday from external ext);

but it gives me the following error: Unexpected token: ( in statement [inse=
rt into internal (name,birthday) (]

I tried with:
2/ insert into internal (name,birthday) select (select p.id from person p w=
here p.name=3Dext.name), ext.birthday from external ext;

but then I get:
Column count does not match in statement [insert into internal (name,birthd=
ay) values (select (select p.id from person p where p.name=3Dext.name),
ext.birthday from external ext)]

And at last:
3/ insert into internal (name,birthday) select (select p.id from person p w=
here p.name=3Dext.name), ext.birthday from external ext;

gives me:
Single value expected

What can I do?

Thanks for any comment :-),
Lo=EFc

ps: 1/ works well under Oracle (replacing now with sysdate)







This message and any attachments (the "message") is intended solely for the=
 addressees and is confidential.=20
If you receive this message in error, please delete it and immediately noti=
fy the sender. Any use not in accord with=20
its purpose, any dissemination or disclosure, either whole or partial, is p=
rohibited except formal approval.=20
The internet can not guarantee the integrity of this message. BNP PARIBAS (=
and its subsidiaries) shall (will) not=20
therefore be liable for the message if modified.=20

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le "message") sont etabli=
s a l'intention exclusive de ses=20
destinataires et sont confidentiels. Si vous recevez ce message par erreur,=
 merci de le detruire et d'en avertir=20
immediatement l'expediteur. Toute utilisation de ce message non conforme a =
sa destination, toute diffusion=20
ou toute publication, totale ou partielle, est interdite, sauf autorisation=
 expresse. L'internet ne permettant pas=20
d'assurer l'integrite de ce message, BNP PARIBAS (et ses filiales) decline(=
nt) toute responsabilite au titre de ce=20
message, dans l'hypothese ou il aurait ete modifie.