CODEWITHSUNDEEP
sqlinformixinsert-select
srinath
srinath

Reputation: 2998

Insert statement with select for only one column in Informix

Is this query 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

Upvotes: 5

Views: 23009

Answers (2)

Thomas Mueller
Thomas Mueller

Reputation: 50087

Most databases (including informix) support insert into ... select:

INSERT INTO emp(emp_id, sal, desg) 
SELECT 111, salary, 'xxx' 
FROM emp_sal 
WHERE emp_id = 222;

⚠ Do note that if a select returns more than one row, more than one row will be inserted.

Upvotes: 9

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

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).

Upvotes: 1

Related Questions