Wednesday, March 7, 2012

How to delete so good?

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?>> But it can works, if at table A (TA) has 1 field to be primary key. If
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|||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)
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.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?
>

No comments:

Post a Comment