Skip site navigation (1) Skip section navigation (2)

Re: Efficient DELETE Strategies

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Manfred Koizar <mkoi-pg(at)aon(dot)at>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christoph Haller <ch(at)rodos(dot)fzk(dot)de>,pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Efficient DELETE Strategies
Date: 2002-08-26 21:35:20
Message-ID: 200208262135.g7QLZKn21408@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Added to TODO:

	* Allow DELETE to handle table aliases for self-joins [delete]

---------------------------------------------------------------------------

Manfred Koizar wrote:
> On Mon, 10 Jun 2002 09:56:27 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
> >Does anyone know whether other systems that support the UPDATE extension
> >for multiple tables also support a DELETE extension for multiple tables?
> >If so, what's their syntax?
> 
> MSSQL seems to guess what the user wants.  All the following
> statements do the same:
> 
> (0)  DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.i=t2.i)
> (1)  DELETE t1 FROM t2 WHERE t1.i=t2.i
> (2a) DELETE t1 FROM t2, t1 WHERE t1.i=t2.i
> (2b) DELETE t1 FROM t2 INNER JOIN t1 ON t1.i=t2.i
> (3a) DELETE t1 FROM t2, t1 a WHERE a.i=t2.i
> (3b) DELETE t1 FROM t2 INNER JOIN t1 a ON a.i=t2.i
> (4a) DELETE a FROM t2, t1 a WHERE a.i=t2.i
> (4b) DELETE a FROM t2 INNER JOIN t1 a ON a.i=t2.i
> (5)  DELETE t1 FROM t1 a
>      WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i)
> (6)  DELETE a FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i)
> 
> (0) is standard SQL and should always work.  As an extension I'd like
> (1) or (2), but only one of them and forbid the other one.  I'd also
> forbid (3), don't know what to think of (4), and don't see a reason
> why we would want (5) or (6).  I'd rather have (7) or (8).
> 
> These don't work:
> (7) DELETE t1 a FROM t2 WHERE a.i = t2.i
> "Incorrect syntax near 'a'."
> 
> (8) DELETE FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i = t2.i)
> "Incorrect syntax near 'a'."
> 
> Self joins:
> (2as) DELETE t1 FROM t1, t1 b WHERE 2*b.i=t1.i
> (4as) DELETE a FROM t1 a, t1 b WHERE 2*b.i=a.i
> (4bs) DELETE a FROM t1 a INNER JOIN t1 b on 2*b.i=a.i
> 
> These don't work:
> DELETE t1 FROM t1 b WHERE 2 * b.i = t1.i
> "The column prefix 't1' does not match with a table name or alias name
> used in the query."
> 
> DELETE t1 FROM t1 a, t1 b WHERE 2 * b.i = a.i
> "The table 't1' is ambiguous."
> 
> And as if there aren't enough ways yet, I just discovered that (1) to
> (6) just as much work with "DELETE FROM" where I wrote "DELETE" ...
> 
> Servus
>  Manfred
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-08-26 21:36:53
Subject: Re: Think I see a btree vacuuming bug
Previous:From: Bruce MomjianDate: 2002-08-26 21:20:24
Subject: Re: Think I see a btree vacuuming bug

pgsql-sql by date

Next:From: Ligia PimentelDate: 2002-08-26 23:54:02
Subject: Problems with version 7.1, could they be fixed in 7.2?
Previous:From: Josh BerkusDate: 2002-08-26 21:11:01
Subject: Re: "reverse()" on strings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group