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

• • Reply
  Cloudfiers

  Member

  36 Points

  257 Posts

  How can perform to delete all rows within a table in a oracle database, which value = 0 for one o...

  Feb 22, 2017 05:09 AM|Cloudfiers|LINK

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

• • Reply
  chandrasheka...

  Star

  8111 Points

  2100 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 22, 2017 02:22 PM|chandrashekar|LINK

  Hi 

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

  Is "ONHANDQUANTITY" column data type is string?

  Thanks.

  YCS
  Please try the answer for the post and finally Don't forget to click “Mark as Answer” on the post that helped you.
  
• • Reply
  Cloudfiers

  Member

  36 Points

  257 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 23, 2017 12:46 AM|Cloudfiers|LINK

  Hello

  it's just decimal 

  public decimal? ONHANDQTY { get; set; }
  
  
  

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

• • Reply
  Cloudfiers

  Member

  36 Points

  257 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 23, 2017 12:58 AM|Cloudfiers|LINK

  chandrashekar

  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?

• • Reply
  chandrasheka...

  Star

  8111 Points

  2100 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 23, 2017 01:44 PM|chandrashekar|LINK

  Hi,

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

  YCS
  Please try the answer for the post and finally Don't forget to click “Mark as Answer” on the post that helped you.
  
• • Reply
  PatriceSc

  All-Star

  42410 Points

  14530 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 23, 2017 02:25 PM|PatriceSc|LINK

  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)

• • Reply
  Cloudfiers

  Member

  36 Points

  257 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 24, 2017 12:39 AM|Cloudfiers|LINK

  chandrashekar

  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..

• • Reply
  Cloudfiers

  Member

  36 Points

  257 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 24, 2017 12:45 AM|Cloudfiers|LINK

  PatriceSc

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

• • Reply
  PatriceSc

  All-Star

  42410 Points

  14530 Posts

  Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...

  Feb 24, 2017 03:07 PM|PatriceSc|LINK

  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)