On Sun, 20 Sep 2015 19:33:35 +0100
Simon Slavin <slavins at bigfraud.org> wrote:
> On 20 Sep 2015, at 7:15pm, James K. Lowden <jklowden at schemamania.org>
> wrote:
>
> > Simon Slavin <slavins at bigfraud.org> wrote:
> >
> >> Constructions like this
> >>
> >>> INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL )
> >>> SELECT 'evil little sister'
> >>
> >> should be rewritten so that you are not trying to do a SELECT in
> >> the middle of your INSERT.
> >
> > Why in the world would you say that? That's the SQL assignment
> > function, the equivalent of
> >
> > A = A + B
>
> I would argue that that would be UPDATE, not INSERT. But I see your
> side of the issue.
I actually think that's the crux of the matter, Simon. If you think
you're operating on a row, then A = A + B is UPDATE, yes. If you think
you're operating on tables, then A = A + B is INSERT, and UPDATE
is more like
A = (A - C) + B
where C is the set of rows being replaced by B.
> Suppose the SELECT doesn't return anything. Do you still want to do
> the INSERT ?
If I said WHERE NOT EXISTS, why would I still want to "do the insert"?!
I'm with Yoda here: there is no try.
> Do you now have to look up the values to INSERT elsewhere ?
No, I do not, not if the values I didn't insert can be specified, e.g.
insert into S
select * from T where exists
(select 1 from R where ... );
If the values can't be specified in like manner, that would suggest to
me a problem with the database design.
TIMTOWTDI, for sure. More than one way to think about it, too. But I
see no downside to using INSERT...SELECT, except that it's startling to
someone unfamiliar with it.
--jkl
