RE: SQL Question: INSERT with SELECT in VALUES section

Richard Try: insert into tab1select "nnn" , t2.fld1 , "mmm" , t2.fld3 , t3.fld7[1,5] etc etc from tab2 t2, tab3 t3 where ..... Keith -> -----Original Message----- -> From: Richard Spitz [mailto:Richard.Spitz@ana.med.uni-muenchen.de] -> Sent: Tuesday, May 13, 2003 10:53 AM -> To: informix-list@iiug.org -> Subject: SQL Question: INSERT with SELECT in VALUES section -> -> -> Hi Informixers, -> -> I know it is possible to use a SELECT statement _instead_ of the -> VALUES section of an INSERT statement, e.g. -> INSERT INTO tab1 (field1, field2) -> SELECT field_a, field_b FROM tab2 -> -> Is there a way to combine that with the VALUES section of the -> INSERT statement? I need to insert a record into a table, but -> this record contains a field which I need to replace via a -> lookup in another table. I tried the following SQL which -> failed: -> -> INSERT INTO tab1 (field1, field2) -> VALUES ("nnnn", (SELECT field_b FROM tab2 WHERE field_a -> = "mmmm")) -> -> Is there a way to achieve this in a single SQL statement, or do -> I have to retrieve the value of field_b with a SELECT INTO statement -> first and then do the INSERT? -> -> Engine version is IDS 7.31UD5 -> -> Regards, Richard -> -> -> -> -- -> +-------------------------------+---------------------------- -> -----------+ -> | Dr. med Richard Spitz | Mail: -> spitz@ana.med.uni-muenchen.de | -> | Klinik f'r Anaesthesiologie | Tel : -> +49-89-7095-6110 | -> | Klinikum der Univ. M'nchen | FAX : +49-89-7095-6420 -> | -> | 81366 M'nchen, Germany | GSM : +49-172-8933578 -> | -> +-------------------------------+---------------------------- -> -----------+ -> ********************************************************************************** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. **********************************************************************************