From: | Nabil Sayegh <postgresql(at)e-trolley(dot)de> |
---|---|
To: | papapep <papapep(at)gmx(dot)net> |
Cc: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Inserting data of two other tables [Now deleting ...] |
Date: | 2003-05-27 12:01:05 |
Message-ID: | 1054036865.6563.24.camel@billy |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Am Die, 2003-05-27 um 12.50 schrieb papapep:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Nabil Sayegh wrote:
>
> | DELETE FROM a WHERE datum='17.07.1999'::date
> ~ ^^^^^^^^^^^^
> This data is not in the "a" table, is in the b one. And the records I
> need to remove are the ones from the b table. Will it work this way?
Then you have to do the opposite:
DELETE FROM b WHERE datum='17.07.1999'::date AND NOT EXISTS (SELECT NULL
FROM a WHERE a.field=b.field)
This query DELETEs rows from b where b.datum is a given date BUT ONLY
these rows where NO field in a EXISTS that refers to the rows in b.
The subquery together with NOT EXISTS returns bool. As we don't need to
return any info from a I just select NULL.
(I didn't test it, but you should get the idea behind it)
>
> | AND NOT EXISTS (SELECT NULL FROM b WHERE b.field=a.field);
>
> What does this part do? It selects rows where b.field and a.field are
> not the same? And the "AND NOT EXISTS"? It inverts the query? I mean, if
No, it selects rows where b.field and a.field _ARE_ the same.
This is just a JOIN. Usually you do this with foreign keys.
b
--------------------------
id_person name datum
1 John 10.03.1995
2 Peter 12.03.1997
3 Alice 17.07.1999
id_person is a PRIMARY KEY here
a
--------------------------
id_person article price
1 scanner 500
1 monitor 300
2 Camera 1000
id_person is a FOREIGN KEY here
DELETE FROM b WHERE datum='17.07.1999'::date AND NOT EXISTS (SELECT NULL
FROM a WHERE a.id_person=b.id_person)
This would delete Alice from b because she hasn't a row in a.
> the intraparenthesys part selects the rows where b.field <> a.field,
> after you change the "sign", selecting the rows that DO have the
> b.field=a.field?
>
> I'm a bit confused...
You should read about FOREIGN KEYs
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de
From | Date | Subject | |
---|---|---|---|
Next Message | Nabil Sayegh | 2003-05-27 13:15:50 | automatic detection of nomore references rows |
Previous Message | Nabil Sayegh | 2003-05-27 11:36:40 | Re: Double quoted column within CREATE TABLE statement |