INSERT INTO ... UNION - unsupported!?
Matthew Cornell
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
And your INSERT statement was...
Obnoxio The Clown
<cor...@cs.umass.edu> broke a vow of silence to utter:
It seems you are not destined to like it. Go back to SQL server or Cloudscape.
Everything about Informix is clearly a pain in the butt. FWIW, I don't think a
view will work, but why not just do a couple of INSERTs? Why does it have to be
a UNION, rather than separate INSERTs?
Matthew Cornell
>
> 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
Matthew Cornell
> Everything about Informix is clearly a pain in the butt.
I'm sorry, you are right - I do sound whiny, and everyone on this
newsgroup has been very helpful. Regarding Informix, we're still willing
to be convinced, esp. by real users like yourself. I guess that our
expectations need adjusting - I was thinking Informix provides features
out the wazoo, great performance, and stellar standards compliance.
After all, it is one of the "big 3" (or 4 or whatever). Instead we're
seeing something more organic - features that are strong in some areas,
but stragely weak or lacking in others. Given adequate time to port, it
wouldn't be too bad, but deadline pressures combined with the port
taking an unexpectely long time... Anyway, sorry.
> FWIW, I don't think a
> view will work, but why not just do a couple of INSERTs? Why does it have to be
> a UNION, rather than separate INSERTs?
We wanted the UNION's distict feature, i.e., we only want unique records
from the separate SELECTs. I guess we could change our code to do each
select in order, and inserting each subsequent one based on whether
there's a match or not...
matt-mr-tail-between-his-legs
Bogdan Neagu
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
Obnoxio The Clown
<cor...@cs.umass.edu> broke a vow of silence to utter:
>> It seems you are not destined to like it. Go back to SQL server or Cloudscape.
>> Everything about Informix is clearly a pain in the butt.
>
>I'm sorry, you are right - I do sound whiny, and everyone on this
>newsgroup has been very helpful. Regarding Informix, we're still willing
>to be convinced, esp. by real users like yourself. I guess that our
Real users like myself? I just consult, mate, I don't actually *do* anything.
:0)
>expectations need adjusting - I was thinking Informix provides features
>out the wazoo, great performance, and stellar standards compliance.
>After all, it is one of the "big 3" (or 4 or whatever). Instead we're
>seeing something more organic - features that are strong in some areas,
>but stragely weak or lacking in others. Given adequate time to port, it
>wouldn't be too bad, but deadline pressures combined with the port
>taking an unexpectely long time... Anyway, sorry.
>
>> FWIW, I don't think a
>> view will work, but why not just do a couple of INSERTs? Why does it have to be
>> a UNION, rather than separate INSERTs?
>
>We wanted the UNION's distict feature, i.e., we only want unique records
>from the separate SELECTs. I guess we could change our code to do each
>select in order, and inserting each subsequent one based on whether
>there's a match or not...
Or you could put a UNIQUE constraint on the table, build a cursor, insert into
the table and just ignore duplicate errors.
Where's Paul Brown when we need him?
Andrew Hamm
>In the year of Our Lord Wed, 13 Dec 2000 16:48:36 -0500, Matthew Cornell
><cor...@cs.umass.edu> broke a vow of silence to utter:
>
>
into
>the table and just ignore duplicate errors.
>
>Where's Paul Brown when we need him?
Who's Paul Brown?
I'm surprised to see the rule against UNION in the statement. Had to grab
the manual to confirm it - sorry - I don't want to sound like I don't
believe you.
Have you tried defining a view which is the UNION? It may not work however,
because Informix views tend to act like macros, so the same technical
limitation blocking a straight-up UNION may well kick in.
Don't be afraid of temp tables. They can be quite efficient in Informix,
especially if you have explicit temp table dbspaces with the T bit switched
on to prevent logging. Or, if you create a temp table explicitly, use the
WITH NO LOG syntax at the end for similar performance-enhancing results.
I noticed from the manual that stored procedures can be used, so that's
another possible solution. No doubt you have to use a "cursory" stored
procedure. Is that a dumb label for a style of SPL or what???
Finally, Obbies suggestion (can I be that familiar? there's a Monty Python
sketch in there somewhere) of using constraints and ignoring errors
triggered me to remember good ol' violation tables. This is a concept
whereby you setup attached tables which receive naughty rows that violate
table constraints in the face of INSERT, UPDATE and I believe even DELETES.
Check out the START VIOLATIONS TABLE ... statement.
I've had some good results using that to simplify data loads and to
simultaneously catch the naughty rows for further examination - exactly what
they were designed for! Yay!
__END__
Andrew Hamm
Technical Consultant
Sanderson Australia Pty Ltd
e-mail: <mailto:ah...@sanderson.net.au>
web: <http://www.sanderson.net.au>
--
I like cats too - let's exchange recipies
Obnoxio The Clown
<ah...@sanderson.net.au> broke a vow of silence to utter:
>Obnoxio The Clown wrote in message <3a37f9cd...@News.CIS.DFN.DE>...
>>
>>Where's Paul Brown when we need him?
>
>Who's Paul Brown?
Oh, he's just this guy who knows stuff.
>Finally, Obbies suggestion (can I be that familiar? there's a Monty Python
>sketch in there somewhere) of using constraints and ignoring errors
>triggered me to remember good ol' violation tables.
"Triggered"? Groan.
allenj
Obnoxio The Clown wrote:
> >Who's Paul Brown?
>
> Oh, he's just this guy who knows stuff.
ROFL..........
Norman Erickson Lugtu
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...@cs.umass.edu>,
Sent via Deja.com
http://www.deja.com/
William Rice
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...@cs.umass.edu>,
Matthew Cornell <cor...@cs.umass.edu> wrote:
William Rice
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>,
Andrew Hamm
>>Finally, Obbies suggestion (can I be that familiar? there's a Monty Python
>>sketch in there somewhere) of using constraints and ignoring errors
>>triggered me to remember good ol' violation tables.
>
>"Triggered"? Groan.
>
Insert default rider of choice:
(pun intended)
(no pun intended)
swart...@gmail.com
Informix's INSERT INTO seems to only allow one SELECT statement.
The solution is to select from the union.
INSERT INTO mytbl
SELECT * FROM
(
SELECT "B", "B" FROM mytblB
UNION
SELECT "C", "C" FROM mytblB
);