Q:

Consecutive SQL statements with state

I'm writing a simple messaging program, where there is a table of messages, which can be claimed by users and have stuff done to them by that user. It isn't predestined which user will claim a given message and so I want a query to select the first of all the available messages, which I have, and then one to mark that message as take, which I also have. The problem is, that I don't want two users using it at the same time to claim the same message and so I want to run the two statements consecutively, without having to go back to the program to find out what to run next in between statements. I believe I can run two consecutive statements by separating them with semi-colons but I want to use data returned in the first query as part of the second. Variables would be perfect, but as far as I'm aware they don't exist in SQL. Is there any way I can preserve state between queries?

A:

Transactions is a good way to go, like le dorfier says, but there are alernatives:
You could do the update first, i.e. tagging a message with the user id or similar. You dont mention which sql flavour youre using, but in mysql, i think it'd look something like this:
UPDATE message
SET    user_id = ...
WHERE  user_id = 0   -- Ensures no two users gets the same message
LIMIT 1

In ms sql, it'd be something along the lines of:
WITH q AS (
  SELECT TOP 1
  FROM message m
  WHERE user_id = 0
) 
UPDATE q
SET    user_id = 1

/B

