> Do you really need the nested select? Hmmm for my dynamic statement construction, it is easier :) but I can use your solution with some changes to reflect the exactly same behaviour of Oracle:
insert into internal (name,birthday) select p.id, ext.birthday from external ext left outer join person p on p.name=ext.name; because your statement: insert into internal (name,birthday) select p.id, ext.birthday from person p, external ext where p.name=ext.name; does not insert the (null | 12-02-2003) tuple ;-) According to my need, I'll need to do about 40 left outer join, are there any limitation about the number of outer join I can make in hsqldb? Well I'll check it out :-) Thank you! Loïc Internet [EMAIL PROTECTED]@lists.sourceforge.net - 03/12/2003 04:12 Veuillez répondre à [EMAIL PROTECTED] Envoyé par : [EMAIL PROTECTED] Pour : hsqldb-user cc : Objet : RE: [Hsqldb-user] Problem with insert into ... select ... Do you really need the nested select? I havent tried this SQL in HSQLDB, but here it is anyhow: insert into internal (name,birthday) select p.id, ext.birthday from person p, external ext where p.name=ext.name; > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, 3 December 2003 2:29 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: [Hsqldb-user] Problem with insert into ... select ... > > 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 table: > 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=ext.name), ext.birthday from external ext); > > but it gives me the following error: Unexpected token: ( in > statement [insert into internal (name,birthday) (] > > I tried with: > 2/ insert into internal (name,birthday) select (select p.id > from person p where p.name=ext.name), ext.birthday from external ext; > > but then I get: > Column count does not match in statement [insert into > internal (name,birthday) values (select (select p.id from > person p where p.name=ext.name), ext.birthday from external ext)] > > And at last: > 3/ insert into internal (name,birthday) select (select p.id > from person p where p.name=ext.name), ext.birthday from external ext; > > gives me: > Single value expected > > What can I do? > > Thanks for any comment :-), > Loïc > > 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. > If you receive this message in error, please delete it and > immediately notify the sender. Any use not in accord with its > purpose, any dissemination or disclosure, either whole or > partial, is prohibited except formal approval. > The internet can not guarantee the integrity of this message. > BNP PARIBAS (and its subsidiaries) shall (will) not therefore > be liable for the message if modified. > > --------------------------------------------- > > Ce message et toutes les pieces jointes (ci-apres le > "message") sont etablis a l'intention exclusive de ses > destinataires et sont confidentiels. Si vous recevez ce > message par erreur, merci de le detruire et d'en avertir > immediatement l'expediteur. Toute utilisation de ce message > non conforme a sa destination, toute diffusion ou toute > publication, totale ou partielle, est interdite, sauf > autorisation expresse. L'internet ne permettant pas d'assurer > l'integrite de ce message, BNP PARIBAS (et ses filiales) > decline(nt) toute responsabilite au titre de ce message, dans > l'hypothese ou il aurait ete modifie. > > > > ------------------------------------------------------- > This SF.net email is sponsored by: SF.net Giveback Program. > Does SourceForge.net help you be more productive? Does it > help you create better code? SHARE THE LOVE, and help us > help YOU! Click Here: http://sourceforge.net/donate/ > _______________________________________________ > Hsqldb-user mailing list > [EMAIL PROTECTED] > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > ------------------------------------------------------- This SF.net email is sponsored by OSDN's Audience Survey. Help shape OSDN's sites and tell us what you think. Take this five minute survey and you could win a $250 Gift Certificate. http://www.wrgsurveys.com/2003/osdntech03.php?site _______________________________________________ Hsqldb-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-user This message and any attachments (the "message") is intended solely for the addressees and is confidential. If you receive this message in error, please delete it and immediately notify the sender. Any use not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval. The internet can not guarantee the integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will) not therefore be liable for the message if modified. --------------------------------------------- Ce message et toutes les pieces jointes (ci-apres le "message") sont etablis a l'intention exclusive de ses destinataires et sont confidentiels. Si vous recevez ce message par erreur, merci de le detruire et d'en avertir immediatement l'expediteur. Toute utilisation de ce message non conforme a sa destination, toute diffusion ou toute publication, totale ou partielle, est interdite, sauf autorisation expresse. L'internet ne permettant pas d'assurer l'integrite de ce message, BNP PARIBAS (et ses filiales) decline(nt) toute responsabilite au titre de ce message, dans l'hypothese ou il aurait ete modifie. ------------------------------------------------------- This SF.net email is sponsored by OSDN's Audience Survey. Help shape OSDN's sites and tell us what you think. Take this five minute survey and you could win a $250 Gift Certificate. http://www.wrgsurveys.com/2003/osdntech03.php?site=8 _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
