How to delete so good?

21 Jul 2005 6:10 PM

Bpk. Adi Wira Kusuma

I ask to you. How to delete data at table A that exist at table B.
Usually I write like this:

DELETE FROM TA where NOID in (SELECT NOID FROM TB).

But it can works, if at table A (TA) has 1 field to be primary key. If table
A (TA) has 4 fields to be primary key. How its syntax so good?

21 Jul 2005 6:19 PM

Anith Sen

>> But it can works, if at table A (TA) has 1 field to be primary key. If
>> table A (TA) has 4 fields to be primary key. How its syntax so good?

You can re-write it with EXISTS like:

DELETE FROM tbl1
WHERE EXISTS ( SELECT *
                  FROM tbl2
                 WHERE tbl2.col1 = tbl1.col1
                   AND tbl2.col2 = tbl1.col2
                   AND tbl2.col3 = tbl1.col3
                   AND tbl2.col4 = tbl1.col4 ) ;

--
Anith

21 Jul 2005 6:25 PM

--CELKO--

DELETE FROM Foobar
WHERE EXISTS
            (SELECT *
                 FROM Barfoo AS B
              WHERE Foobar.c1 = B.c1
                   AND Foobar.c2 = B.c2
                   AND Foobar.c3 = B.c3
                   AND Foobar.c4 = B.c4);

21 Jul 2005 6:26 PM

JT

Let's assume that in TB has a primary key that consists of SSN and
DateOfBirth. Let's also assume that TA basically contains the same type of
records but is denormalized so that the same key is consolidated in a column
called PersonID.

delete from TA where PersonID in (select SSN + DateOfBirth from TB)

Show quote

"Bpk. Adi Wira Kusuma" <adi_wira_kus***@yahoo.com.sg> wrote in message
news:ek6us%23hjFHA.3164@TK2MSFTNGP15.phx.gbl...
>I ask to you. How to delete data at table A that exist at table B.
> Usually I write like this:
>
> DELETE FROM TA where NOID in (SELECT NOID FROM TB).
>
> But it can works, if at table A (TA) has 1 field to be primary key. If
> table
> A (TA) has 4 fields to be primary key. How its syntax so good?
>
>