Re: Bug or stupidity

I didn't see that join syntax in the documentation for delete, thanks
for pointing it out.

MS SQL Server syntax for a delete is a little less confusing, IMHO.

instead of DELETE FROM x WHERE x.a = table.a and x.b > table.b and table.c = 4;
they have DELETE x FROM x join table on x.a = table.a and x.b > table.b and table.c = 4

the table being deleted from is listed separately, but you can still
have full join syntax (including outer joins) to help with the
deletion.

This is similar to the current PostGreSQL update syntax, except that
the table being updated is not part of the from and therefore can only
be connected through an inner join, not an outer join.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

On Tue, Oct 26, 2004 at 06:21:23PM +0200, Thomas Hallgren wrote:
> Do you consider this overly complex? Compare:
>
> DELETE FROM x WHERE EXISTS (SELECT * FROM table WHERE x.a = table.a and
> x.b > table.b and table.c = 4)
>
> to:
>
> DELETE FROM x, table WHERE x.a = table.a and x.b > table.b and table.c = 4
>
> In the latter, what is it you are deleting? Is it x or table? I'm not at
> all in favor of listing several tables in the FROM clause of a DELETE
> statement (that includes implicitly adding them).

The problem is that in DELETE, there is no FROM clause in the sense
there is with the other commands, the FROM keyword is used for a
different purpose. The FROM clause the tables are automatically added
to does not have an equivalent in the original SQL statement.

I'm in favour of the status quo, exactly the current default behaviour.
That second example you give is confusing and should be disallowed. But
no-one has come up with anything better. Do you have a better
suggestion, other than forbidding the currently allowed syntax?

> >Every DB interface I've used so far displays the notices
> >where I can see them. This notice is one of the less useful, there
> >are other more useful warnings which are much more handy to see...
> >
> >
> Right. Useful "warnings"! Seems you agree that this should be a warning,
> not a notice.

Hmm, I consider a notice to be a warning anyway, something you should
always read. The default log level is notice anyway, so if you're
seeing warnings, you'll see the notices too...

Anyway, I think the reasoning so far is, the default stays as it is
until someone comes up with a non-confusing way of adding a real FROM
clause to DELETEs. Requiring people upgrading to add missing tables in
the FROM for SELECT and UPDATE is one thing. Asking them to rewrite
every DELETE query as a subselect is a bit too far. It would be nice
also because then you could then also use aliases.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.