Re: Adding missing FROM-clause entry in subquery

On 2 Oct 2003, Robert Treat wrote:

> On Thu, 2003-10-02 at 08:58, btober@seaworthysys.com wrote:
> >
> > INSERT INTO report (
> >   SELECT * FROM temptable s1 EXCEPT
> >   SELECT * FROM report s2 WHERE s2.title = report.title);
> >
>
> i think you're essentially doing:
>
> INSERT INTO report (
>     SELECT * FROM temptable s1 EXCEPT
>     SELECT * FROM report s2, report WHERE s2.title = report.title);
>
> which i don't think is what you expected to happen*, but seems to be
> pretty much equivalent in this case.
>
>
>
> *given that missing-from behaves differently with inserts and updates, i
> can see how this is pretty confusing...

But in the update case it's not an error because of the extension that allows
the where clause of an update to refer to other tables (I think). Whereas in
the insert case there is no cross over between the table named for insert and
the select cluase generating the data to inserted, it is a complete select
clause in it's own right. Indeed, having written that it's occured to me the
reason behind this specific example, it's to avoid the insert of duplicate key
error. The select in the insert statement should stand as a query in it's own
right but if it didn't all those people looking to avoid transaction abort when
wanting an update or insert if not there already type operation would have
their work around.

> in 7.4 theres an option to turn
> this off, though you'd need to think of another way to do what your
> doing above since it would break

--
Nigel J. Andrews