CODEWITHSUNDEEP
sqlnetezza
drmirror
drmirror

Reputation: 3760

Update a table while reading it

I have a database table with a Y/N flag in one column. I want to read all records where the flag is 'N' and after processing a record, set the flag to 'Y' in that record. Is it correct, and reasonable, to do this at the same time, using two separate connections? Or should I read the entire table first and update only after I'm done with the reading? What's the correct approach to this?

The database involved is Netezza, in case it matters.

Upvotes: 0

Views: 1042

Answers (2)

sam yi
sam yi

Reputation: 4934

You should read first then update. Not asynchronsly. If the "select" part takes a long time, you should consider doing it batches. You can use a separate connections but should be confident you've completed your read.

Upvotes: 1

Sharpling
Sharpling

Reputation: 2559

Depends mostly on your design and needs.

How important is the flag? What if something goes wrong when you have set all flags before you have processed them... and so on.

Why you need two connections is out of my understanding, usually you have one connection you keep open. I don't know the blocks of Netezza but some system can also be made to do select and update at the same time.

You could do:

  1. Load a bunch, process them and then update all flags. (fastest, one fail = all fail)
  2. Load a bunch, process one, update one flag, process next.. (quite fast and one fail don't hit em all)
  3. Fetch and update them one by one. (will be slowest but most secure)

Upvotes: 1

Related Questions