Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: 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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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
      );

  4. #4
    Join Date
    Oct 2003
    Posts
    54
    I think your statement is close however it returns well over 9000 records where my original select statement only returns 463?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is your data, after all ... if this statement is close, enhance it a little bit and it'll be just fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •