Delete statement trouble

heprox · #1 (**permalink**) 08-26-04, 14:56

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?

Pat Phelan · #2 (**permalink**) 08-26-04, 15:35

My first observation is that you might have better luck posting an Oracle question in the Oracle 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

Littlefoot · #3 (**permalink**) 08-26-04, 16:23

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

Code:

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
  );

heprox · #4 (**permalink**) 08-26-04, 17:00

I think your statement is close however it returns well over 9000 records where my original select statement only returns 463?

Pat Phelan · #5 (**permalink**) 08-26-04, 17:05

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

Littlefoot · #6 (**permalink**) 08-27-04, 02:11

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

Internet Services

Web Development

Digital Marketing

Consumer Tech