INSERT INTO ... UNION - unsupported!?
A new Informix user found that IDS 9.21 on Linux doesn't allow UNION (or ORDER BY, FIRST, INTO TEMP) in the SELECT part of an INSERT, unlike SQL Server, and asked for workarounds for a query inserting linked object IDs into graph_objects. Repliers suggested rewriting the two UNIONed SELECTs as a single SELECT with OR'd join conditions (noting possible loss of index usage), an equivalent version using two IN subqueries, or building the rows in a temp table (SELECT ... INTO TEMP plus a second INSERT, using an outer join to exclude existing ids) and then inserting from it. No confirmation from the poster that any version worked is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi Folks, We have some SQL that inserts a UNION into a table. It works with SQL Server 7.0 and Cloudscape, but not on Informix IDS.2000 9.21.UC3-2 on Red Hat Linux 6.2. From the SQL Syntax docs on INSERT: > As indicated in the diagram for INSERT on page 2-535, not all clauses and > options of the SELECT statement are available for you to use in an INSERT > statement. > The following SELECT clauses and options are not supported: > n FIRST > n INTO TEMP > n ORDER BY > n UNION * I can't believe a product this mature would have such a significant limitation. What a major pain in the butt! Would someone please tell me 1) that I'm not having a nightmare - this limitation is reality, and if so, 2) how people work around it? (I've considered inserting into a temporary table, and using views, though I think the latter won't work with the INSERT.) We are new Informix users and we really *want* to like the product, but jeez! matt
We got your statement about limitations and immaturity (of Informix, of course). And your INSERT statement was... Matthew Cornell wrote: > Hi Folks, > > We have some SQL that inserts a UNION into a table. It works with SQL > Server 7.0 and Cloudscape, but not on Informix IDS.2000 9.21.UC3-2 on > Red Hat Linux 6.2. From the SQL Syntax docs on INSERT: > > > As indicated in the diagram for INSERT on page 2-535, not all clauses and > > options of the SELECT statement are available for you to use in an INSERT > > statement. > > The following SELECT clauses and options are not supported: > > n FIRST > > n INTO TEMP > > n ORDER BY > > n UNION * > > I can't believe a product this mature would have such a significant > limitation. What a major pain in the butt! Would someone please tell me > 1) that I'm not having a nightmare - this limitation is reality, and if > so, 2) how people work around it? (I've considered inserting into a > temporary table, and using views, though I think the latter won't work > with the INSERT.) > > We are new Informix users and we really *want* to like the product, but > jeez! > > matt
Bogdan Neagu wrote:
>
> We got your statement about limitations and immaturity (of Informix, of course).
Quite right - I apologize for the bluster; frustration due to deadlines
and new tools.
> And your INSERT statement was...
Again, sorry - we have a number of places where it's used. Here's one
example:
INSERT INTO graph_objects (id, status, selected)
SELECT DISTINCT object.id, 'N', 'T'
FROM object, link, graph_objects
WHERE (link.o1_id = object.id AND
link.o2_id = graph_objects.id AND
graph_objects.selected = 'T' AND
NOT object.id IN
(SELECT id
FROM graph_objects))
UNION
SELECT DISTINCT object.id, 'N', 'T'
FROM object, link, graph_objects
WHERE (link.o2_id = object.id AND
link.o1_id = graph_objects.id AND
graph_objects.selected = 'T' AND
NOT object.id IN
(SELECT id
FROM graph_objects))
matt
Hi, Matt.
I think the default for UNION is DISTINCT, so duplicates are removed.
So your INSERT could be (I haven't tested it)
INSERT INTO graph_objects (id, status, selected)
SELECT DISTINCT object.id, 'N', 'T'
FROM object, link, graph_objects
WHERE (
(link.o1_id = object.id AND link.o2_id = graph_objects.id)
OR
(link.o2_id = object.id AND link.o1_id = graph_objects.id )
) AND
graph_objects.selected = 'T' AND
NOT object.id IN
(SELECT id
FROM graph_objects))
The drawback is that it will probabily NOT use index paths because of the OR. I
don't know if the sub-query on graph_objects works in an INSERT. Anyone ?
And because you test that the id is not there already, you might use Obnoxio's
suggestion to have multiple inserts.
Cheers,
Bogdan
Try this SQL, give me feedback.
INSERT INTO graph_objects (id, status, selected)
SELECT DISTINCT object.id, 'N', 'T'
FROM object
WHERE (object_id IN (SELECT link.o1_id
FROM link, graph_objects
WHERE link.o2_id = graph_objects.id AND
graph_objects.selected = 'T') OR
object_id IN (SELECT link.o2_id
FROM link, graph_objects
WHERE link.o1_id = graph_objects.id AND
graph_objects.selected = 'T')) AND
NOT object.id IN (SELECT id FROM graph_objects);
Erickson
In article <3A37EBFF.4F4EBEC0@cs.umass.edu>,
Matthew Cornell <cornell@cs.umass.edu> wrote:
> Bogdan Neagu wrote:
> >
> > We got your statement about limitations and immaturity (of
Informix, of course).
>
> Quite right - I apologize for the bluster; frustration due to
deadlines
> and new tools.
>
> > And your INSERT statement was...
>
> Again, sorry - we have a number of places where it's used. Here's one
> example:
>
> INSERT INTO graph_objects (id, status, selected)
> SELECT DISTINCT object.id, 'N', 'T'
> FROM object, link, graph_objects
> WHERE (link.o1_id = object.id AND
> link.o2_id = graph_objects.id AND
> graph_objects.selected = 'T' AND
> NOT object.id IN
> (SELECT id
> FROM graph_objects))
> UNION
> SELECT DISTINCT object.id, 'N', 'T'
> FROM object, link, graph_objects
> WHERE (link.o2_id = object.id AND
> link.o1_id = graph_objects.id AND
> graph_objects.selected = 'T' AND
> NOT object.id IN
> (SELECT id
> FROM graph_objects))>
> matt
>
Sent via Deja.com
http://www.deja.com/
All standard disclaimers about SQL written without being tested apply.
If there is a unique contstraint on graph_objects.id(which it lookes
like there is) The following might accomplish what you want and be a
bit faster than the given method even if it worked.
SELECT DISTINCT object.id id, go2.id exists
FROM object, link, graph_objects, outer graph_objects go2
WHERE link.o1_id = object.id
AND link.o2_id = graph_objects.id
AND graph_objects.selected = 'T'
AND go2.id=object.id
INTO TEMP otc WITH NO LOG;
INSERT INTO otc
SELECT DISTINCT object.id id, go2.id exists
FROM object, link, graph_objects, outer graph_objects go2
WHERE link.o2_id = object.id
AND link.o1_id = graph_objects.id
AND graph_objects.selected = 'T'
AND go2.id=object.id
INTO temp table otc with no log;
INSERT INTO graph_objects(id, status, selected)
SELECT DISTINCT object.id, 'N', 'T'
FROM otc
WHERE exists is null
Hope this helps,
Will
In article <3A37EBFF.4F4EBEC0@cs.umass.edu>,
Matthew Cornell <cornell@cs.umass.edu> wrote:
> Bogdan Neagu wrote:
> >
> > We got your statement about limitations and immaturity (of
Informix, of course).
>
> Quite right - I apologize for the bluster; frustration due to
deadlines
> and new tools.
>
> > And your INSERT statement was...
>
> Again, sorry - we have a number of places where it's used. Here's one
> example:
>
> INSERT INTO graph_objects (id, status, selected)
> SELECT DISTINCT object.id, 'N', 'T'
> FROM object, link, graph_objects
> WHERE (link.o1_id = object.id AND
> link.o2_id = graph_objects.id AND
> graph_objects.selected = 'T' AND
> NOT object.id IN
> (SELECT id
> FROM graph_objects))
> UNION
> SELECT DISTINCT object.id, 'N', 'T'
> FROM object, link, graph_objects
> WHERE (link.o2_id = object.id AND
> link.o1_id = graph_objects.id AND
> graph_objects.selected = 'T' AND
> NOT object.id IN
> (SELECT id
> FROM graph_objects))>
> matt
>
Sent via Deja.com
http://www.deja.com/
Slight correction seeing exists is a key word it might be better not to
use it as a column name :)
SELECT DISTINCT object.id id, go2.id junk
FROM object, link, graph_objects, outer graph_objects go2
WHERE link.o1_id = object.id
AND link.o2_id = graph_objects.id
AND graph_objects.selected = 'T'
AND go2.id=object.id
INTO TEMP otc WITH NO LOG;
INSERT INTO otc
SELECT DISTINCT object.id id, go2.id junk
FROM object, link, graph_objects, outer graph_objects go2
WHERE link.o2_id = object.id
AND link.o1_id = graph_objects.id
AND graph_objects.selected = 'T'
AND go2.id=object.id
INTO temp table otc with no log;
INSERT INTO graph_objects(id, status, selected)
SELECT DISTINCT object.id, 'N', 'T'
FROM otc
WHERE junk is null
In article <91bcdu$dt3$1@nnrp1.deja.com>,
William Rice <ricew@operamail.com> wrote:
> All standard disclaimers about SQL written without being tested
apply.
>
> If there is a unique contstraint on graph_objects.id(which it lookes
> like there is) The following might accomplish what you want and be a
> bit faster than the given method even if it worked.
>
> SELECT DISTINCT object.id id, go2.id exists
> FROM object, link, graph_objects, outer graph_objects go2
> WHERE link.o1_id = object.id
> AND link.o2_id = graph_objects.id
> AND graph_objects.selected = 'T'
> AND go2.id=object.id
> INTO TEMP otc WITH NO LOG;>
> INSERT INTO otc
> SELECT DISTINCT object.id id, go2.id exists
> FROM object, link, graph_objects, outer graph_objects go2
> WHERE link.o2_id = object.id
> AND link.o1_id = graph_objects.id
> AND graph_objects.selected = 'T'
> AND go2.id=object.id
> INTO temp table otc with no log;>
> INSERT INTO graph_objects(id, status, selected)
> SELECT DISTINCT object.id, 'N', 'T'
> FROM otc
> WHERE exists is null>
> Hope this helps,
> Will
>
> In article <3A37EBFF.4F4EBEC0@cs.umass.edu>,
> Matthew Cornell <cornell@cs.umass.edu> wrote:
> > Bogdan Neagu wrote:
> > >
> > > We got your statement about limitations and immaturity (of
> Informix, of course).
> >
> > Quite right - I apologize for the bluster; frustration due to
> deadlines
> > and new tools.
> >
> > > And your INSERT statement was...
> >
> > Again, sorry - we have a number of places where it's used. Here's
one
> > example:
> >
> > INSERT INTO graph_objects (id, status, selected)
> > SELECT DISTINCT object.id, 'N', 'T'
> > FROM object, link, graph_objects
> > WHERE (link.o1_id = object.id AND
> > link.o2_id = graph_objects.id AND
> > graph_objects.selected = 'T' AND
> > NOT object.id IN
> > (SELECT id
> > FROM graph_objects))
> > UNION
> > SELECT DISTINCT object.id, 'N', 'T'
> > FROM object, link, graph_objects
> > WHERE (link.o2_id = object.id AND
> > link.o1_id = graph_objects.id AND
> > graph_objects.selected = 'T' AND
> > NOT object.id IN
> > (SELECT id
> > FROM graph_objects))> >
> > matt
> >
>
> Sent via Deja.com
> http://www.deja.com/
>
Sent via Deja.com
http://www.deja.com/