Re: Efficient DELETE Strategies

Bruce Momjian wrote:
> ...
> Yes, another keyword is the only solution.  Having FROM after DELETE
> mean something different from FROM after a tablename is just too weird.
> I know UPDATE uses FROM, and it is logical to use it here, but it is
> just too wierd when DELETE already has a FROM.  Should we allow FROM and
> add WITH to UPDATE as well, and document WITH but support FROM too?  No
> idea.  What if we support ADD FROM as the keywords for the new clause?

Sounds like the best solution so far. 

Christopher Kings-Lynne wrote:
> DELETE [LOW_PRIORITY | QUICK] table_name[.*] [,table_name[.*] ...]
>        FROM table-references
>        [WHERE where_definition]
>
> or
>
> DELETE [LOW_PRIORITY | QUICK]
>        FROM table_name[.*], [table_name[.*] ...]
>        USING table-references
>        [WHERE where_definition]
>
> ...
> The idea is that only matching rows from the tables listed before the FROM
> or before the USING clause are deleted. The effect is that you can delete
> rows from many tables at the same time and also have additional tables that
> are used for searching.

Sounds tempting. It is much more what I was asking for. 
Is there a collision with USING ( join_column_list ) ? 
And it looks like very much work for the HACKERS. 

Hannu Krosing wrote:
> ...
> Or then we can just stick with standard syntax and teach people to do
>
> DELETE FROM t1 where t1.id1 in
>  (select id2 from t2 where t2.id2 = t1.id1)
>
> and perhaps even teach our optimizer to add the t2.id2 = t1.id1 part
> itself to make it fast
>
> AFAIK this should be exactly the same as the proposed
>
> DELETE FROM t1 FROM t2
> WHERE t2.id2 = t1.id1

This is a fine idea. But it looks like very much work for the HACKERS, too. 

Regards, Christoph