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
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 work
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 solutions
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 may
[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
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 do
[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
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
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, and it
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
[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 rows, millions,
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
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
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.
And I
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 gather
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.
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
references
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
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;
END;
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;
COMMIT;
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 reservations
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 reservation_id =
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_id IN
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 FROM isuse WHERE
27 matches
Mail list logo
