Postgres Pro
Downloads
Home   >   mailing lists

Thread: deletion of records

deletion of records

From
"Dorward Villaruz"
Date:
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.

Re: deletion of records

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

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')";
>
> 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.

Re: deletion of records

From
Patrick Welche
Date:
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


> 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')";

Re: deletion of records

From
frbn
Date:
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 :)

Re: deletion of records

From
Chris Albertson
Date:
--- 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