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

    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)

    ...the primary key for the table is the is the ROWID. 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
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the primary key for the table is the is the ROWID
    This is a VERY BAD thing to do.
    ROWID's should NEVER be stored in a table.
    Bad, bad, bad!
    Once again go to http://asktom.oracle.com
    do a keyword search on DUPLICATE RECORDS
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2003
    Posts
    54
    This database is a production instance for an enterprise ERP application. I have absolutely no say in how they structured their DB...I'm just attempting to create a script for a datafix, and I need to figure out the syntax for the statement, but htanks anyway....

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If your SELECT shows you the records you need to delete, then you can turn it into a delete like this:
    Code:
    delete from gm_inv_cst where rowid in
    (
    select gm_inv_cst.rowid
    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
    );

Posting Permissions

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