Re: insert into ... select ... and column order

Tore Halset wrote:
> Hello.
>
> One of our users tried a "insert into ... select ..." that gave a
> strange error message. After digging into the issue, the problem seem to
> be that the order of the columns in the select statement must match the
> table definition. Here is a way to reproduce this case.

> insert into dest_2 select USER_ID, PRODUCT_ID, min(PERMIT_START_DATE) as
> PERMIT_START_DATE, max(PERMIT_END_DATE) as PERMIT_END_DATE from source
> group by USER_ID, PRODUCT_ID;
>
> Why does the column order matter when the subselect has all the correct
> column names?

The names do not matter - the database won't try to match up the names.
Think about it in comparison with INSERT ... VALUES - it's the same layout.

What you need to do is supply the column-names for the insert (this is a
good idea anyway - it makes it explicit what is going on and will cope
better if you change the definition of dest_2).

INSERT INTO dest_2 (permit_end_date, permit_start_date, ...)
SELECT <column for permit_end_date>, <column for permit_start_date>, ...

--
Richard Huxton
Archonet Ltd