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
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
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
At 10:05 AM 8/7/2008, [EMAIL PROTECTED] wrote:
Date: Thu, 7 Aug 2008 09:14:49 -0700
From: [EMAIL PROTECTED]
To: [email protected]
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
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
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.
--
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
[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 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] 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
[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
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
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
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 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 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
[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 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
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 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 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
21 matches
Mail list logo
