How to select and update a record at the same time in mySQL?

Question

Is there any way to select a record and update it in a single query? I tried this:

UPDATE arrc_Voucher 
  SET ActivatedDT = now() 
WHERE (SELECT VoucherNbr, VoucherID
         FROM arrc_Voucher
        WHERE ActivatedDT IS NULL
          AND BalanceInit IS NULL
          AND TypeFlag = 'V'
        LIMIT 1 )

which I hoped would run the select query and grab the first record that matches the where clause, the update the ActivatedDT field in that record, but I got the following error:

1241 - Operand should contain 1 column(s)

Any ideas?

Answer 1

How about:

UPDATE arrc_Voucher 
  SET ActivatedDT = NOW() 
WHERE ActivatedDT IS NULL
  AND BalanceInit IS NULL
  AND TypeFlag = 'V'
LIMIT 1;

Answer 2

From the MySQL API documentation :

UPDATE returns the number of rows that were actually changed

You cannot select a row and update it at the same time, you will need to perform two queries to achieve it; fetch your record, then update it.

If you are worrying about concurrent processes accessing the same row through some kind of race condition (supposing your use case involve high traffic), you may consider other alternatives such as locking the table (note that other processes will need to recover--retry--if the table is locked while accessing it)

Or if you can create stored procedure, you may want to read this article or the MySQL API documentation.

But about 99% of the time, this is not necessary and the two queries will execute without any problem.