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