Delete statement trouble

I have an Oracle 8i database with a table called GM_INV_CST. This table has the following fields:

STORE_CD NOT NULL VARCHAR2(4)
SKU_NUM NOT NULL VARCHAR2(12)
PLUS_MINUS NOT NULL VARCHAR2(1)
RCV_DT NOT NULL DATE
UNIT_CST NOT NULL NUMBER(10,2)
QTY NOT NULL NUMBER(6)
CST_TOT NOT NULL NUMBER(13,2)

I'm using the following statement to identify duplicate entries of based on SKU_NUM, STORE_CD, and PLUS_MINUS, then identify the entries with zero (0) quantities in QTY and CST_TOT:

select base.cnt,base.sku_num,base.store_cd,gm_inv_cst.plu s_minus,
gm_inv_cst.rcv_dt,gm_inv_cst.unit_cst,gm_inv_cst.q ty,gm_inv_cst.cst_tot
from gm_inv_cst,
(select count(*)as cnt,sku_num,store_cd,plus_minus
from gm_inv_cst
group by sku_num,store_cd,plus_minus
having count(*)>1) base
where gm_inv_cst.cst_tot = 0
and gm_inv_cst.qty = 0
and gm_inv_cst.sku_num=base.sku_num
and gm_inv_cst.store_cd=base.store_cd
and base.plus_minus = gm_inv_cst.plus_minus

...the problem is I can't seem to find the right syntax to create a delete statement for these records, anyone?My first observation is that you might have better luck posting an Oracle question in the Oracle (http://www.dbforums.com/f4) forum. The folks that hang out in the engine specific forums can often suggest specific features of a given engine that make the job a lot simpler than doing it the way that is required by the SQL standard.

The next observation is that you haven't specified the primary key column(s). This would help a lot to determine the simplest answer to your question.

-PatP|||If you already identified duplicate records, can't you use this query to delete them? Minor modification would then be

DELETE FROM gm_inv_cst
WHERE (plus_minus, rcv_dt, unit_cst, qty, cst_tot) IN
(SELECT g.plus_minus, g.rcv_dt, g.unit_cst, g.qty, g.cst_tot
FROM gm_inv_cst g,
(SELECT COUNT(*) AS cnt, sku_num, store_cd, plus_minus
FROM gm_inv_cst
GROUP BY sku_num, store_cd, plus_minus
HAVING COUNT(*) > 1
) base
WHERE g.cst_tot = 0
AND g.qty = 0
AND g.sku_num = base.sku_num
AND g.store_cd = base.store_cd
AND g.plus_minus = base.plus_minus
);|||I think your statement is close however it returns well over 9000 records where my original select statement only returns 463?|||Your original consolidates the duplicates, there have to be at least twice as many rows in the target table as in your result set, and quite possibly more than that!

-PatP|||It is your data, after all ... if this statement is close, enhance it a little bit and it'll be just fine :)

Delete Records!

Hi,
I am trying to delete records from a table when a specified column encounters null values in them. Could you please let me know how!
ThanksRefer to your other post (http://www.dbforums.com/t926583.html) in this forum.

You can use DELETE FROM tablename WHERE Columnname=null and refer to books online for more information.|||columnname is null

instead of columnname = null|||Oh yeah, thanks for the make-up.

DELETE problem

I have a hierarchy of menu and sub menus setup using this table:
CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30)NOT NULL,
parentID INTNULL --Null If Root Menu
)

I want all sub menus to be deleted when a parent menu is deleted. I
wasn't able to get a recursive procedure to work because I got an error
about multiple cursors w/ the same name.

The best I've come up w/ is this:

DELETE FROM menu WHERE id = x --Del Menu
--Cleanup Children
DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
from menu)

Is there a better/faster way of doing this?Hi

If you did not see my reply to your previous post, the following is about
the best you can do

DELETE FROM menu WHERE id = x --Del Menu

WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM menu
WHERE parentID IS NOT NULL
AND parentID NOT IN (SELECT id FROM menu)
END

Do not use <> NULL

John

<wackyphill@.yahoo.com> wrote in message
news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
>I have a hierarchy of menu and sub menus setup using this table:
> CREATE TABLE menu
> (
> id INT NOT NULL IDENTITY PRIMARY KEY,
> name VARCHAR(30) NOT NULL,
> parentID INT NULL --Null If Root Menu
> )
> I want all sub menus to be deleted when a parent menu is deleted. I
> wasn't able to get a recursive procedure to work because I got an error
> about multiple cursors w/ the same name.
> The best I've come up w/ is this:
> DELETE FROM menu WHERE id = x --Del Menu
> --Cleanup Children
> DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
> from menu)
> Is there a better/faster way of doing this?|||John Bell wrote:
> Hi
> If you did not see my reply to your previous post, the following is
about
> the best you can do
> DELETE FROM menu WHERE id = x --Del Menu
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM menu
> WHERE parentID IS NOT NULL
> AND parentID NOT IN (SELECT id FROM menu)
> END
> Do not use <> NULL
> John
> <wackyphill@.yahoo.com> wrote in message
> news:1103306757.201621.73930@.z14g2000cwz.googlegro ups.com...
> >I have a hierarchy of menu and sub menus setup using this table:
> > CREATE TABLE menu
> > (
> > id INT NOT NULL IDENTITY PRIMARY KEY,
> > name VARCHAR(30) NOT NULL,
> > parentID INT NULL --Null If Root Menu
> > )
> > I want all sub menus to be deleted when a parent menu is deleted. I
> > wasn't able to get a recursive procedure to work because I got an
error
> > about multiple cursors w/ the same name.
> > The best I've come up w/ is this:
> > DELETE FROM menu WHERE id = x --Del Menu
> > --Cleanup Children
> > DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT
id
> > from menu)
> > Is there a better/faster way of doing this?|||Thanks, John. That works great!|||Sorry John I did miss your last post and you are right, that works very
well. Thank you very much.|||Look up the nested sets model for trees; this can be done without
procedural code.

DELETE on FK

I am having 2 tables :

products :

product_id (PK, int, not null)
reports_id (FK, int, not null)
name

reports :

reports_id (PK, int , not null)
dateR

If I : DELETE FROM reports WHERE reports_id = 100

how can I create a contraints in MS SQL 2000 that automaticly all the products with reports_id = 100 are deleted too ?

thank youChecked the "Cascade Delete Related Records" From Relationship|||but how can I do it as a script (FROM outside)
ALTER TABLE ... CONTRAINST ... ?

thank you|||Check this...

ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check FOREIGN KEY
(
[reports_id]
) REFERENCES [reports] (
[reports_id]
) ON DELETE CASCADE|||thank you rudra !|||Rudra I am having a probleme the fields are not declared as FK in the database ( I have not created that database) can I use a ON DELETE CASCADE anyway on 2 similar fields ?

something like

ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check ???
(
[reports_id]
) REFERENCES [reports] (
[reports_id]
) ON DELETE CASCADE

thank you|||why don't you create a on-delete trigger for this?|||i dont know how to do it and i run the commands from a .NET application|||Why are you trying to do this from a user interface? The job of writing triggers belongs to the developer, not the application.|||i want to put it in any way in the database no matter the way|||A trigger is a permanent database object. An application has no business creating such things on the fly.
Either create the trigger or don't, but don't ask us to help you shoot yourself in the foot.
If you insist upon handling relational integrity within the interface, then make two calls to the database. One to delete the child records, and one to delete the parent record.|||thank you for the trigger, and helping me for database code

for the rest i never worry for what i cannot change