locked
How can perform to delete all rows within a table in a oracle database, which value = 0 for one of the columns? RRS feed

  • Question

  • User-1790113996 posted

    basically, it's like this:

    The table is called PH.INVENTORY, the column name is ONHANDQUANTITY

    I want to run a query which deletes all rows in PH.INVENTORY table in which the ONHANDQUANTITYvalue is 0 (All rows contain a 0 value for the column ONHANDQUANTITY)

    how can I add in my delete query?

    separate thanks..

    public void Delete(ItemModel Item)
    {
        Conn = ORCONN.con;
        if (Conn.State != ConnectionState.Open)
        {
            Conn.Open();
        }
    
        try
        {
            cmd.Connection = Conn;
    
            var query = "DELETE FROM PH.INVENTORY WHERE INVNO LIKE '" + Item.INVNO.Trim() + "%'";
    
    
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        finally
        {
            Conn.Close();
        }
    }
    Wednesday, February 22, 2017 5:09 AM

All replies

  • User-1516073966 posted

    Hi 

    "DELETE FROM PH.INVENTORY WHERE INVNO LIKE '" + Item.INVNO.Trim() + "%' AND ONHANDQUANTITY=0"

    Is "ONHANDQUANTITY" column data type is string?

    Thanks.

    Wednesday, February 22, 2017 2:22 PM
  • User-1790113996 posted

    Hello

    it's just decimal 

    public decimal? ONHANDQTY { getset; }


    this is Updated , is it right?

    public
     void Delete(ItemModel Item)         {             Conn = ORCONN.con;             if (Conn.State != ConnectionState.Open)             {                 Conn.Open();             }             try             {                 cmd.Connection = Conn;                 var query = "DELETE FROM PH.INVENTORY WHERE INVNO LIKE '" + Item.INVNO.Trim() + "%' AND ONHANDQTY=0";                 cmd.CommandText = query;                 cmd.CommandType = CommandType.Text;                 cmd.ExecuteNonQuery();             }             finally             {                 Conn.Close();             }         }
    Thursday, February 23, 2017 12:46 AM
  • User-1790113996 posted

    Hi 

    "DELETE FROM PH.INVENTORY WHERE INVNO LIKE '" + Item.INVNO.Trim() + "%' AND ONHANDQUANTITY=0"

    Is "ONHANDQUANTITY" column data type is string?

    Thanks.

    I tried this method  but didn't not work , any idea sir?

    Thursday, February 23, 2017 12:58 AM
  • User-1516073966 posted

    Hi,

    What I am asking is in database table, what is the data type of the column "ONHANDQUANTITY"? Is it Integer/nvarchar?

    Thursday, February 23, 2017 1:44 PM
  • User753101303 posted

    Hi,

    did not work

    It's best to tell what happens. Do you have an error message (for example could it be that you try to use LIKE on INVNO which might be a numeric column, or it could be that ONHANDQUANTITY is actually NULL rarther than 0 and so it doesn't delete the row you think should be deleted etc...)

    You perhaps want WHERE etc... AND (ONHANQUANTITY=0 OR ONHANDQUANTITY IS NULL)

    Thursday, February 23, 2017 2:25 PM
  • User-1790113996 posted

    Hi,

    What I am asking is in database table, what is the data type of the column "ONHANDQUANTITY"? Is it Integer/nvarchar?

    This is a number , am i asking how can i perform separation deletion on ONHANDQTY ? or other method of deletion 

    regard

    Cloudfiers

    thanks..

    Friday, February 24, 2017 12:39 AM
  • User-1790113996 posted

    Hi,

    Cloudfiers

    did not work

    It's best to tell what happens. Do you have an error message (for example could it be that you try to use LIKE on INVNO which might be a numeric column, or it could be that ONHANDQUANTITY is actually NULL rarther than 0 and so it doesn't delete the row you think should be deleted etc...)

    You perhaps want WHERE etc... AND (ONHANQUANTITY=0 OR ONHANDQUANTITY IS NULL)

    This is the right method? deletion together of INVO AND ONHANDQTY


    var
     query = "DELETE FROM PH.INVENTORY WHERE INVNO LIKE '" + Item.INVNO.Trim() + "%' AND (ONHANQUANTITY=0 OR ONHANDQUANTITY IS NULL)";                cmd.CommandText = query;                cmd.CommandType = CommandType.Text;                cmd.ExecuteNonQuery();


    Friday, February 24, 2017 12:45 AM
  • User753101303 posted

    It seems it should work. If not, once again please tell what happens. Do you have an error message? Also ExecuteNonQuery returns the number of affected rows so if 0 it is definitively that the WHERE clause doesn't match any row.

    Not directly related but using parameters is preffered over string concatenation (for example your code will fail if the user enters ' and is subject to https://en.wikipedia.org/wiki/SQL_injection)

    Friday, February 24, 2017 3:07 PM