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

Re: DELETE with JOIN

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: DELETE with JOIN
Date: 2008-08-07 20:01:29
Message-ID: 489B5499.90401@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-sql
felix(at)crowfix(dot)com wrote:
> On Thu, Aug 07, 2008 at 03:00:35PM -0400, Frank Bax wrote:
>> If you're really desperate; is it possible to alter table 'a' to add column 
>> b_id; populate it; delete your rows without a join; then drop the column?
> 
> I thought of something similar, but UPDATE has the same limitation as DELETE.


Instead of DELETE; what if you CREATE a new table of the rows you wish 
to keep (using JOIN instead of IN).  Then either drop original table and 
rename new table OR delete all rows and re-populate from new table.

You mentioned that the process of insert/delete is to be repeated.  Are 
all the rows that were inserted; the same ones that will be deleted when 
the cycle is complete?  If yes; then after you delete this batch of 
rows; add a 'junky' column and populate with any value different from 
the default.  Your mass-insert should populate with the default value; 
and you can delete based on this value.

In response to

Responses

pgsql-sql by date

Next:From: Mark RobertsDate: 2008-08-07 20:27:00
Subject: Re: DELETE with JOIN
Previous:From: Tom LaneDate: 2008-08-07 19:58:51
Subject: Re: DELETE with JOIN

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