sqlinformixinsert-select

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

Solution

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