Re: Inserting data of two other tables [Now deleting ...]

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