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,
Hello, Picavet.
> Anybody for a ray of light on a different approach ? This look like a
> recurrent problem, isn't there an experienced sql programmer here who
> tackled this issued a couple of time ?
Actually, I'm not very experienced in SQL. But from my point of view
this problem could be solve
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.
--
Hello,
Here is a sql problem, which I thought simple at first, but for which I
ended up with a solution I find surprisingly complicated.
I really think I could have achieved a much easier way of handling this,
but I do not manage to find the trick allowing a very simple and
efficient query to solv
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
Oh, I checked the function. There are some syntax errors. Right code
listed below:
CREATE OR REPLACE FUNCTION cr_tbls_by_staid() RETURNS INTEGER AS
$$
DECLARE
stid INTEGER;
q TEXT;
BEGIN
FOR stid IN SELECT staid FROM mytest LOOP
q:= 'CREATE TABLE "s' || staid || '" (staid varchar(50), val re
21 matches
Mail list logo
