Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

share|improve this answer
    
Hello Chris, thank you very much for your answer. Unfortunately I forgot to mention my environment. I will add the data to my question. Sorry for the inconvenience. –  AlexTr Oct 4 '12 at 12:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.