Insert statement with select for only one column in Informix

Question

Just need to know whether this query is possible in Informix.

insert into emp(emp_id, sal, desg)
values (111, (select salary from emp_sal where emp_id=222), 'xxx');

Table structures are:

emp: emp_id, name, sal, desg

emp_sal: emp_id, sal

Solution 1

I didn't try with Informix but most databases support insert into ... select:

insert into emp(emp_id, sal, desg) 
select 111, salary, 'xxx' 
from emp_sal where emp_id = 222;

Solution 2

The statement as written should work as long as the sub-query returns a single row.

Proof of Concept:

SQL[1871]: create temp table x(i integer, j integer, s char(10));
SQL[1872]: insert into x(i,j,s) values(1, (select atomic_number from elements where name = 'Carbon'), "Elephant");
SQL[1873]: select * from x;
1|6|Elephant
SQL[1874]: 

My test database has a table of elements in it, hence the sub-select works for me. Warning: I tested on 11.70.FC6, not 7.31. Your mileage may vary given that you appear to be using a much older version of Informix (7.31 was first released before Y2K, IIRC, though 7.31.UDn was a fix-pack from the mid-2000's, probably circa 2005).