Delete Statement with MultiPart Key

• Mark Moss

  Valued Member

  Points: 64

  More actions

  October 25, 2005 at 12:36 am

  #169204

  Ladies / Gentlemen

       Here is the Delete statement that I would like to emulate, but I need to do it with a multipart key.

       Delete from TableA

                 where TableA.ID

                 in ( Select TableB.Id from TableB )

       The Four part key consists of the following fields

            StoreId               int

            VendorNumber    int

            ItemNumber       char(15)

            PLUNumber         bigint

       So I need to match

  TableA.StoreID           : TableB.StoreID

  TableA.VendorNumber : TableB.VendorNumber

  TableA.ItemNumber     : TableB.ItemNumber

  TableA.PLUNumber      : TableB.PLUNumber

  Thanks in advance

  Mark Moss

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  October 25, 2005 at 1:03 am

  #599722

  DELETE FROM TableA

  WHERE EXISTS (

   SELECT *

   FROM TableB

   WHERE StoreID = TableA.StoreID

   AND VendorNumber = TableA.VendorNumber

   AND ItemNumber = TableA.ItemNumber

   AND PLUNumber = TableA.PLUNumber)

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/