Skip site navigation (1) Skip section navigation (2)

Re: Delete performance

From: Jana <jana(dot)vasseru(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Delete performance
Date: 2009-06-15 13:33:45
Message-ID: op.uvkimjhng6o41l@truhlik (view raw or flat)
Thread:
Lists: pgsql-novice
On Sun, 14 Jun 2009 18:12:50 +0200, Frank Bax <fbax(at)sympatico(dot)ca> wrote:

> Jana wrote:
>>  Hello,
>> i have a table with about 250m records from which i want to delete  
>> thoose not contained in other table. I used this SQL query:
>> DELETE FROM data_structures_items WHERE id_data_structure NOT IN (
>> SELECT id_structure FROM data_structures);
>
>
> DELETE FROM data_structures_items, data_structures WHERE  
> data_structures_items.id_data_structure =  
> data_structures.id_data_structure AND  
> data_structures_items.id_data_structure IS NULL;
>

Thanks for answer, this however is not a valid syntax (at least according  
to manual, and my 8.3)
version. What could be done is

DELETE FROM data_structures_items USING data_structures
WHERE
data_structures_items.id_data_structure=data_structures.id_structure
AND something

but the problem is in that "something". I cannot write
data_structures_items.id_data_structure =
data_structures.id_data_structure AND
data_structures_items.id_data_structure IS NULL;

because it a) doesn't make sense (column can't be null and equal to
something at the same time), b) doesn't select what i want (rows whoose
id_data_structure is NOT in the data_structures table, i'm pretty sure it
is a number ). I can't join tables in DELETE command, and i can't think of  
a way doing it with WHERE

Regards,
  Jana

In response to

Responses

pgsql-novice by date

Next:From: Phillip SitbonDate: 2009-06-15 18:34:40
Subject: Re: Delete performance
Previous:From: Frank BaxDate: 2009-06-14 17:21:36
Subject: Re: Delete performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group