Mark exported rows as exported

Question

I need to write an export module which marks exported rows as exported. We currently run SQL Server Express 2012, but may switch to Oracle, so I need a client-side approach. We are currently using ASP.NET 4.5.

I have a boolean field named "exported" which is supposed to be set to true.

My idea was to collect all affected rows IDs in an array in the client software and build something like

Metacode:

FOREACH(affectedRowsIDs as affectedRowID)
  $sql = "udpate myRows SET exported = 1 where id = $affectedRowID)
  execute $sql

The problem would be a lot of queries to run.

Different thought:

FOREACH(affectedRowsIDs as affectedRowID)
  $affRows += " , $affectedRowID"

$sql = "udpate myRows SET exported = 1 where id IN ($affRows)"

Could you please give me some feedback how you would do it?

Answer 1

The best solution will likely depend on your database server. In PostgreSQL, the best way would be something like this:

 BEGIN; -- start transaction
 UPDATE myrows SET exported = true WHERE exported = false
 RETURNING *;
 -- do some work in the app to process these rows
 COMMIT; -- if successful, else ROLLBACK;

If that's not possible, then create a temp table, put the info you want to export in there, update exported based on the records in the temp table, and then commit once the transaction is complete. Remember to clean up your temp table.

Answer 2

Its depends on your situation Like if you are fetching all records of table then you directly mark the exported using below query

UDPATE myRows SET exported = 1 WHERE exported != 1

If you are exporting based on some condition on same table then you can put the conditions in where clause like above

UDPATE myRows SET exported = 1 WHERE {Conditios}

If you are getting the ids in same stored procedures then you can put all these ids in temp table and can put the join or exists statement like below

UPDATE myrows
SET exported=1
WHERE EXISTS(SELECT id FROM myrows innter WHERE inner.id=myrows.id)

If you are getting all exported ids from client side or c# code then you can pass this comma separated string in stored procedure parameter and then can split it and can update like below query

UPDATE myrows
SET exported=1
WHERE exists(SELECT id FROM splitfunction(@exportedIds) temp WHERE temp.id=myrows.id)

If you have any other scenarios then please explain in comment, I will share the query for same.