InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:04:04 GMT20http://www.sqlservercentral.com/Forums/Topic1410644-338-1.aspx[quote][b]thbaig1 (1/23/2013)[/b][hr]can I DELETE DATA FROM tblQuantityP.ProductId in a way IF parent TABLE has NOT record against it ?e.g. Prod1 with ProductId 1 removed from tblProduct, can I DELETE ONly ProductId 1 FROM ALL occurance IN tblQuantityP.ProductId?[/quote] Something like this ? [code="sql"]delete p from tblQuantityP Where exists (select 2 from tblProduct t where t.ProductId = p.ProductId and p.ProductId = 1)[/code]Thu, 24 Jan 2013 01:00:37 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1410644-338-1.aspxTABLE tblProductProductId int Productname varchar(20)TABLE tblQuantityPQuantityPIdQuantity intPrice intProductId XMLDATA IN tblProductProductId ProductName1 Prod12 Prod23 Prod3 4 Prod45 Prod56 Prod6DATA IN tblQuantityPQuantityPId Quantity Price ProductId 1 12 12 <itemids> <itemid>1</itemid> <itemid>2</itemid> <itemid>3</itemid></itemids>2 11 21 <itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>3 11 11 <itemids> <itemid>222</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>4 134 11 <itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>5 112 11 <itemids> <itemid>2</itemid> <itemid>5</itemid> <itemid>6</itemid></itemids>Now I can JOIN XML COLUMN TO mother TABLE TO GET DATA AS following . (suggest IF SOME more optimized way: Not real question)SELECT qp.QuantityPId , Prod.ProductId , Prod.ProductNameFROM dbo.tblQuantityP qp CROSS APPLY ProductId.nodes('itemids/itemid') AS xmlitems ( itemid ) JOIN dbo.tblProduct Prod ON Prod.ProductId = xmlitems.ProductId.value('.', 'int'); [b]My Question[/b] is can I DELETE DATA FROM tblQuantityP.ProductId in a way IF parent TABLE has NOT record against it ?e.g. Prod1 with ProductId 1 removed from tblProduct, can I DELETE ONly ProductId 1 FROM ALL occurance IN tblQuantityP.ProductId?Wed, 23 Jan 2013 08:55:36 GMTthbaig1