DELETE SQL QUESTION

[Kerpal]

I have a Delete question:
Say you have a QTY table, with field AMNT and field SKU_ID
Then you have a SKU table with field SKU_ID and ITEM_ID and LOC_ID
Then a LOC table with fields LOC_ID and LOC_NAME
And finally a ITEM table with ITEM_ID and ITEM_NAME.

I want to delete from QTY where LOC_NAME = 'US'
but the only way I know how is use a sub-select:

DELETE FROM QTY WHERE SKU_ID IN
(SELECT SKU_ID FROM SKU, LOC, ITEM WHERE
SKU.LOC_ID = LOC.LOC_ID AND SKU.ITEM_ID = ITEM.ITEM_ID AND LOC_NAME ='US');

Is there a better way?

 

[JediDan]

I don't really see any other option for you... why don't you want to use a sub-select?

Also, in this particular case, you might be able to eliminate two tables out of the FROM clause of the sub-select:

DELETE FROM QTY 
WHERE SKU_ID IN (SELECT SKU_ID 
                 FROM SKU
                 WHERE LOC_ID = whatever_the_USA_loc_id_is)

Wouldn't that also work in your case?

 

[sem]

I suppose that ITEM table is unnecessary:

DELETE FROM QTY WHERE SKU_ID IN
(SELECT SKU_ID FROM SKU, LOC WHERE
SKU.LOC_ID = LOC.LOC_ID AND LOC_NAME ='US');

Regards, Dima

 

[Kerpal]

That's too bad that there are no other alternatives. Because what I really have is:

DELETE Cells.Qty WHERE Cells.Version_ID IN
(SELECT Version_ID FROM Version,Enterprise,Component, Planning_Item,Location,Product WHERE
Version.Enterprise_ID=Enterprise.Enterprise_ID AND Version.Component_ID=Component.Component_ID AND
Component.Name='CONSENSUS FORECASE LINE' AND
Version.Planning_Item_ID=Planning_Item.Planning_Item_ID AND
Planning_Item.Location_ID=Location.Location_ID AND
Planning_Item.Product_ID=Product.Product_ID);


Probably screw some data integrity issues though between all those tables. Anyway, thanks for taking a look at it.

 

[haraldstuff]

DELETE FROM QTY
WHERE exists
(SELECT 1 FROM SKU, LOC, ITEM
WHERE SKU.LOC_ID = LOC.LOC_ID
AND SKU.ITEM_ID = ITEM.ITEM_ID
AND LOC_NAME = 'US'
AND SKU.SKU_ID = QTY.SKU_ID );