Oracle SQL Delete values located in Array/CArray/DataStructure

Question

I have CArray/CMap with IDs.
On the SQL Table there are: ID, Value, StartTime, EndTime.
I receive StartTime and EndTime and need to delete all IDs that appear on that CArray.
So there is a need for query such as:
"delete from table where id in ( list[0], list[1], list[2]. ... list[n] ) and time between T1 and T2"
My problem is how to implement the "in (..)" how to transfer the IDs that on CArray ?

Using CDatabase.executeSQL(QUERY) with CommitTrans and CommitTrans

Thanks

trying to make CArray to CString x, x1, x2, and enter that cstring inside the barracks — ilansch
– ilansch, Commented Nov 28, 2012 at 15:48

cha · Accepted Answer · 2012-11-29 00:45:34Z

1

The simple you can do is this (provided you have CArray and it is called arId):

CString sql, item;
sql = _T("delete from table where id ");
if(arId.GetCount() == 1)
{
  item.Format(_T(" = %d"), arId[0]);
  sql += item;
}
else
{
  for(int i = 0; i < arId.GetCount(); ++i)
  {
    if(i == 0) sql += _T("IN (");
    item.Format(_T("%d"), arId[0]);
    if(i == arId.GetCount()-1)
      item += _T(")");
    else
      item += _T(", ");
    sql += item;
  }
}
CDatabase.executeSQL(sql);

For CMap replace the for(i = 0;...) with the POSITION pos = mapId.GetStartPosition() etc.

answered Nov 29, 2012 at 0:45

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ilansch Over a year ago

Hi, is there a limit to number of IDs i can put inside the in (...) ? if i have million IDs, should i split to chunks and make in several queries ?

cha Over a year ago

I do not believe there is a limit on IDs, however there is a limit on the entire SQL query length. Having said all that, please note, that it is all database specific.

Collectives™ on Stack Overflow

Oracle SQL Delete values located in Array/CArray/DataStructure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related