Remove rows from SQL DB that appear in a Array

Question

I Develop with MFC Visual C++ and Oracle SQL Server.
I have SQL table with: IDs, value and time, when the application insert a new row: some ID, some Value and time being inserted.

My goal is to delete rows of values that were changed between certain time. since the data that was inserted during that time has incorrect value.

Where is the catch ? I dont need to delete all the rows that were updated in that time period, only the rows with IDs that appear on a certain CArray.

I can go through each ID from CArray and execute a delete query to that certain ID in that time period (whether there is entry or not) - problem since i can have 150K IDs to iterate on..

Thanks

Is the ID the primary key? If so, you can just delete anything with the ID and totally ignore when they were added since each ID should be unique. — Chad Campbell
– Chad Campbell, Commented Oct 18, 2012 at 12:57
ID is not primary key, there is no primary key in that table. I can use CRecordSet to poll the rows — ilansch
– ilansch, Commented Oct 18, 2012 at 13:27

Marlin Pierce · Accepted Answer · 2012-10-18 12:58:21Z

1

DELETE FROM table-name WHERE id in (...)

answered Oct 18, 2012 at 12:58

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges

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

Comments

Diego · Accepted Answer · 2012-10-18 12:58:46Z

0

transform your array into a tempTable with one column and then delete from your destiantion table where ID in (select Id from temptable)

Here is an example:

declare @RegionID varchar(50)
SET @RegionID = '853,834,16,467,841'
declare @S varchar(20)
if LEN(@RegionID) > 0 SET @RegionID = @RegionID + ',' 
CREATE TABLE #ARRAY(region_ID VARCHAR(20))

WHILE LEN(@RegionID) > 0 BEGIN
   SELECT @S = LTRIM(SUBSTRING(@RegionID, 1, CHARINDEX(',', @RegionID) - 1))
   INSERT INTO #ARRAY (region_ID) VALUES (@S)
   SELECT @RegionID = SUBSTRING(@RegionID, CHARINDEX(',', @RegionID) + 1, LEN(@RegionID))
END

delete from from your_table
where regionID IN (select region_ID from #ARRAY)

answered Oct 18, 2012 at 12:58

Diego

36.5k21 gold badges94 silver badges138 bronze badges

1 Comment

ilansch Over a year ago

When i make a table from code, If i insert rows to the table, it takes something like 1 minutes for each 20K rows, maybe DB limitation, so to load the CArray with 150K gonna take too long

Collectives™ on Stack Overflow

Remove rows from SQL DB that appear in a Array

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related