On Thu, Aug 07, 2008 at 10:55:17PM -0400, Joe wrote:
> I recall a similar problem ages ago and IIRC it was due to Oracle's locking
> configuration, i.e., some parameter had to be increased and the instance
> restarted so it could handle the transaction (or it had to be done in
> chunks).
I gat
On Thu, Aug 7, 2008 at 5:37 PM, <[EMAIL PROTECTED]> wrote:
> On Thu, Aug 07, 2008 at 06:12:32PM -0400, Alvaro Herrera wrote:
>
>> I suggest you do not assume that Oracle implementation details apply to
>> Postgres, because they do not, most of the time. They certainly don't
>> in this case.
>
> A
On Aug 7, 2008, at 4:37 PM, [EMAIL PROTECTED] wrote:
And I suggest you go back and read where I said I had to do this on
several databases and am trying to avoid custom SQL for each one. I
would much rather this were postgresql only, but it's not.
Then it does appear you have an Oracle debuggi
On Thu, Aug 07, 2008 at 06:12:32PM -0400, Alvaro Herrera wrote:
> I suggest you do not assume that Oracle implementation details apply to
> Postgres, because they do not, most of the time. They certainly don't
> in this case.
And I suggest you go back and read where I said I had to do this on
se
[EMAIL PROTECTED] wrote:
> On Thu, Aug 07, 2008 at 03:58:51PM -0400, Tom Lane wrote:
> > [EMAIL PROTECTED] writes:
> > > I have tried to do this before and always found a way, usually
> >
> > > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?)
> >
> > > but I have too many
On Aug 7, 2008, at 2:39 PM, [EMAIL PROTECTED] wrote:
In this case, the first database I tried was Oracle, and it complained
of too much transactional data; I forget the exact wording now.
You might try it on PostgreSQL. While it might have to spill the
result of the subquery to disk, it shou
On Thu, Aug 07, 2008 at 03:58:51PM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > I have tried to do this before and always found a way, usually
>
> > DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?)
>
> > but I have too many rows, millions, in the IN crowd, ha ha,
On Thu, Aug 07, 2008 at 04:01:29PM -0400, Frank Bax wrote:
> 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
> 'ju
On Thu, 2008-08-07 at 09:14 -0700, [EMAIL PROTECTED] wrote:
>DELETE FROM a WHERE a.b_id = b.id AND b.second_id = ?
>
This should work for your needs:
delete from a
using b
where a.id = b.id -- join criteria
and b.second_id = ?
> I have tried to do this before and always found a way, usually
[EMAIL PROTECTED] 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 limi
[EMAIL PROTECTED] writes:
> I have tried to do this before and always found a way, usually
> DELETE FROM a WHERE a.b_id IN (SELECT id FROM b WHERE second_id = ?)
> but I have too many rows, millions, in the IN crowd, ha ha, and it
> barfs.
Define "barfs". That seems like the standard way to
On Thu, Aug 07, 2008 at 03:00:35PM -0400, Frank Bax wrote:
> Could you not achieve the same result with a LIMIT on subSELECT and reissue
> the command until there is nothing to delete?
Oracle has some barbarous alternative to LIMIT. I find myself
retching over Oracle almost as much as MySQL.
>
[EMAIL PROTECTED] wrote:
On Thu, Aug 07, 2008 at 10:40:22AM -0700, Steve Midgley wrote:
Have you tried something where you read in all those "IN id's" and then
group them into blocks (of say 1,000 or 10,000 or whatever number works
best)? Then execute:
DELETE FROM a WHERE a.b_id in ([static_
On Thu, Aug 07, 2008 at 10:40:22AM -0700, Steve Midgley wrote:
> Have you tried something where you read in all those "IN id's" and then
> group them into blocks (of say 1,000 or 10,000 or whatever number works
> best)? Then execute:
>
> DELETE FROM a WHERE a.b_id in ([static_list_of_ids])
It m
On Thu, Aug 07, 2008 at 05:05:38PM +, Ragnar wrote:
> did you look at DELETE FROM table1 USING table2 WHERE ... ?
No, I hadn't known about that. It looks handy to know about, at
least, but I don't see it for Oracle. I am going to play with that,
but I don't think it will help here.
--
At 10:05 AM 8/7/2008, [EMAIL PROTECTED] wrote:
Date: Thu, 7 Aug 2008 09:14:49 -0700
From: [EMAIL PROTECTED]
To: pgsql-sql@postgresql.org
Subject: DELETE with JOIN
Message-ID: <[EMAIL PROTECTED]>
I want to delete with a join condition. Google shows this is a common
problem, but the only solution
On fim, 2008-08-07 at 09:14 -0700, [EMAIL PROTECTED] wrote:
> I want to delete with a join condition. Google shows this is a common
> problem, but the only solutions are either for MySQL or they don't
> work in my situation because there are too many rows selected. I also
> have to make this wor
I want to delete with a join condition. Google shows this is a common
problem, but the only solutions are either for MySQL or they don't
work in my situation because there are too many rows selected. I also
have to make this work on several databases, includeing, grrr, Oracle,
so non-standard MyS
Bryce Nesbitt wrote:
> Markus Schaber wrote:
>
> > Bryce Nesbitt wrote:
> >
> >
> >> BEGIN;
> >> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> >> WHERE reservation_id IN
> >> (select reservation_id from reservations where date > magic)
> >> );
> >> DELETE F
Markus Schaber wrote:
> Hi, Bryce,
>
> Bryce Nesbitt wrote:
>
>
>> BEGIN;
>> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
>> WHERE reservation_id IN
>> (select reservation_id from reservations where date > magic)
>> );
>> DELETE FROM isuse WHERE reservation_i
Bryce Nesbitt wrote:
> Owen Jacobson wrote:
>
> > BEGIN;
> > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> > WHERE reservation_id = reservation_to_delete);
> > DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> > DELETE FROM reservations WHERE reservati
Hi, Bryce,
Bryce Nesbitt wrote:
> BEGIN;
> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> WHERE reservation_id IN
> (select reservation_id from reservations where date > magic);
> DELETE FROM isuse WHERE reservation_id IN
> (select reservation_id from reserva
Owen Jacobson wrote:
> BEGIN;
> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> WHERE reservation_id = reservation_to_delete);
> DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> DELETE FROM reservations WHERE reservation_id = reservation_to_delete;
> COMM
Owen Jacobson wrote:
> BEGIN;
> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> WHERE reservation_id = reservation_to_delete);
> DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> DELETE FROM reservations WHERE reservation_id =
> reservation_to_delete;
>
BigSmoke wrote:
> ...I'd usually do this by using
> issue_id INTEGER REFERENCES issue ON DELETE CASCADE
>
Good, and valuable, thanks!
But at the moment I can't change the schema. So is there a way to do a
cascaded or joined delete
in a sql schema that did not anticipate it? Again, this is d
Bryce Nesbitt wrote:
> When I delete a record from a certain table, I need to delete a
> (possibly) attached note as well. How can I do this with
> postgres? The
> tables are like this:
>
> reservation
> reservation_id
> stuff...
>
> isuse
> issue_id
> reservation_id
r
I'm not sure if this is true for you as I can't see your complete table
definitions, but I'd usually do this by using
issue_id INTEGER REFERENCES issue ON DELETE CASCADE
in my column definition.
See [1] for more information.
[1]http://www.postgresql.org/docs/current/interactive/ddl-constraint
When I delete a record from a certain table, I need to delete a
(possibly) attached note as well. How can I do this with postgres? The
tables are like this:
reservation
reservation_id
stuff...
isuse
issue_id
reservation_id
stuff..
note
issue_id
text comments...
A s
28 matches
Mail list logo
