deletion of records

Started by Dorward Villaruzalmost 24 years ago5 messagesgeneral

Jump to latest

Dorward Villaruz

dorwardv@ntsp.nec.co.jp

almost 24 years ago

can anybody help me with this one

i want to delete rows from one table based values from another table.

here is the scenario

i have a table named custinfo which contain 2000000 records and a table custinfotemp which contain 30000 records. i want to delete all records in custinfo where the cust_id is found on custinfotemp where it's update status is 'D'

here is my sql statement

psql -h $HOSTNAME -d $DBNAME -c "DELETE FROM custinfo WHERE custinfo.UserID IN (SELECT custinfotemp.userid FROM custinfotemp where updateStatus='D')";

is it possible that this query statement takes 24 hours and still running to execute?

is there a way to achieve the same result faster?

thanks.

frbn

frbn@efbs-seafrigo.fr

over 23 years ago

In reply to: Dorward Villaruz (#1)

Re: deletion of records

DELETE FROM custinfo,custinfotemp
WHERE
custinfo.UserID=custinfotemp.userid
and
custinfotemp.updateStatus='D'
;
should avoid the 2.10^6 selects

Dorward Villaruz a écrit:

Show quoted text

can anybody help me with this one

i want to delete rows from one table based values from another table.

here is the scenario

i have a table named custinfo which contain 2000000 records and a table
custinfotemp which contain 30000 records. i want to delete all records
in custinfo where the cust_id is found on custinfotemp where it's update
status is 'D'

here is my sql statement

psql -h $HOSTNAME -d $DBNAME -c "DELETE FROM custinfo WHERE
custinfo.UserID IN (SELECT custinfotemp.userid FROM custinfotemp where
updateStatus='D')";

is it possible that this query statement takes 24 hours and still
running to execute?

is there a way to achieve the same result faster?

thanks.

Patrick Welche

prlw1@newn.cam.ac.uk

over 23 years ago

In reply to: frbn (#2)

Re: deletion of records

On Fri, Jul 12, 2002 at 09:38:39AM +0200, frbn wrote:

DELETE FROM custinfo,custinfotemp
WHERE
custinfo.UserID=custinfotemp.userid
and
custinfotemp.updateStatus='D'
;
should avoid the 2.10^6 selects

Won't that delete rows from custinfotemp too?

Patrick

Show quoted text

Dorward Villaruz a ï¿½crit:

can anybody help me with this one

i want to delete rows from one table based values from another table.

here is the scenario

i have a table named custinfo which contain 2000000 records and a table
custinfotemp which contain 30000 records. i want to delete all records
in custinfo where the cust_id is found on custinfotemp where it's update
status is 'D'

here is my sql statement

psql -h $HOSTNAME -d $DBNAME -c "DELETE FROM custinfo WHERE
custinfo.UserID IN (SELECT custinfotemp.userid FROM custinfotemp where
updateStatus='D')";

frbn

frbn@efbs-seafrigo.fr

over 23 years ago

In reply to: Dorward Villaruz (#1)

Re: deletion of records

Patrick Welche a écrit:

On Fri, Jul 12, 2002 at 09:38:39AM +0200, frbn wrote:

DELETE FROM custinfo,custinfotemp
WHERE
custinfo.UserID=custinfotemp.userid
and
custinfotemp.updateStatus='D'
;
should avoid the 2.10^6 selects

Won't that delete rows from custinfotemp too?

Patrick

no, as this is syntaxically incorrect. oops

I meant:

delete from custinfo
where custinfo.UserID=custinfotemp.userid
and
custinfotemp.updateStatus='D';

---------------------------------------------
explain delete from t1 where t1.c1 = t2.c1;

Merge Join (cost=200000139.66..200000164.66 rows=10000 width=14)
-> Sort (cost=100000069.83..100000069.83 rows=1000 width=10)
-> Seq Scan on t1 (cost=100000000.00..100000020.00 rows=1000 width=10)
-> Sort (cost=100000069.83..100000069.83 rows=1000 width=4)
-> Seq Scan on t2 (cost=100000000.00..100000020.00 rows=1000 width=4)

ps: An interesting notice in the errlog:

NOTICE: Adding missing FROM-clause entry for table "t2"

I don't know how to remove this *non*enoying notice :)

Chris Albertson

chrisalbertson90278@yahoo.com

over 23 years ago

In reply to: Dorward Villaruz (#1)

Re: deletion of records

--- Dorward Villaruz <dorwardv@ntsp.nec.co.jp> wrote:

here is my sql statement

psql -h $HOSTNAME -d $DBNAME -c "DELETE FROM custinfo WHERE
custinfo.UserID IN (SELECT custinfotemp.userid FROM custinfotemp
where updateStatus='D')";

Try this

DELETE FROM custinfo
WHERE
custinfo.UserID = custinfotemp.userid AND
custinfotemp.updateStatus = 'D';

It would be good to have indexs on UserID on both tables.

Seeing a table named "custinfotemp" kind of raises a red
flag. Possibly you may want to re-think the design if
you are moving data between tables that is really the
same data.

=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org

__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com