-1

In a mySQL table inv there are 3 columns : ID, num and state.
state has 3 values : 0 = unused, 1 = used, 2 = reserved.
To get the next unused num I use this query : 

SELECT num FROM inv WHERE state = 0

When the query returns an num I need to update the record and set state to 2 = reserved. 

UPDATE inv SET state = 2 Where ID = id

I know there is a way to to do this in one query only, but how ?

Any helping answer is appreciated

EDIT :

I am trying to achive this :

I need to generate invoices with unique numbers. So I fetch the next free , unused number whith state = 0, then return this number and set in the same query the returned record to state = 2, so nobody else will use it in the meanwhile. Later when I succeed to generate a flawless invoice I update this record with state = 1 (used). In case generating an invoice flawless fails I set the state back to 0 = unused.

Hope I could express my approach more clearly.

S.K. Venkat
  • 1,749
  • 2
  • 23
  • 35
Ben
  • 677
  • 5
  • 19
  • why not use "if exists"? that could be a lot easier. you may find it [here](http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) or why not include it in the where clause. – bipartite May 31 '16 at 07:37
  • 1
    Your best bet would be to use a stored procedure to get a num out, update it, and return it, but you will also be subject to race conditions so be careful with concurrency. You'd probably be better off explaining the problem you are trying to solve rather than the current state of the database. – lc. May 31 '16 at 07:44
  • See my edits above. Whats the -1 for ? – Ben May 31 '16 at 08:07

4 Answers4

2

update inv set state = 2 where id in (select id from inv where state = 0 limit 1);

This will get the next single num value and update it as reserved.

Updated answer :

My apologies for given wrong answer since above said query will not work due to the limit clause is restricted for using with subquery in MySQL.

So I have proposed another approach which is similar to the answers given here :

update inv a join (select id, num from inv where state=0 limit 1) b
 on a.id = b.id SET state=2;

This query has been verified with sample data.

S.K. Venkat
  • 1,749
  • 2
  • 23
  • 35
  • 2
    This definitely answers the question in its current form. Now the reason I haven't personally offered this is because I'll bet $5 they're going to want to know which one was just updated and return that to the calling code. But I'm probably reading into it too much :-P – lc. May 31 '16 at 07:47
  • Returns FALSE. Also where do your statement return a number ? – Ben May 31 '16 at 11:52
  • The select query on behalf of join operand will return an ID and based on that ID, the status column will be updated on the fly. This is what can be achieved from single line query. Be clear what do you want to do, an update or fetching a number? – S.K. Venkat May 31 '16 at 13:00
  • Well if you read my question above there it is written : `To get the next unused num`. What is unclear about this ? Again: I need to fetch a number, set status to 2, use it in an invoice and update after flawless invoice generation the record state with 1. So fetching it and set status to 2 I want to do in one statement (transaction). – Ben May 31 '16 at 13:55
  • For your requirement, single line query is not correct approach. You should use Stored Procedures or triggers in MySQL to achieve the result. Refer the answer provided by @Reno http://stackoverflow.com/a/37539840/2383136 – S.K. Venkat May 31 '16 at 14:17
  • @lc. Now I got your point. I wasn't clear about the requirement even after read your comment. Thanks... – S.K. Venkat May 31 '16 at 14:30
  • okay, so also here my question how to express @Reno's answer in PDO statements ? – Ben May 31 '16 at 15:15
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/113434/discussion-between-s-k-venkat-and-ben). – S.K. Venkat May 31 '16 at 15:21
1

Try this;)

SET @num = null;
SET @id = null;
SELECT id into @id FROM inv WHERE state = 0 LIMIT 1;
SELECT num into @num FROM inv WHERE id = @id;

PREPARE stmt FROM 'UPDATE inv SET state = 2 Where ID = ?' USING @id;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT @num;
Blank
  • 12,308
  • 1
  • 14
  • 32
0
update inv a join (select num from inv where state= 0) b on a.id = b.id
SET state = 2;
Priyanshu
  • 885
  • 6
  • 12
  • Not only does `b` not have a column `id`, but this is also wrong. See my comment on @hashbrown's answer. – lc. May 31 '16 at 07:40
  • While your suggestion might resolve the original question, some explanation on how your suggestion is solving the issue might contribute to better insight with the OP and other readers of the answer later on. – rpy May 31 '16 at 10:46
-1

Does this work?

UPDATE inv SET state = 2 Where ID IN (SELECT id FROM inv WHERE state = 0)
hashbrown
  • 3,438
  • 1
  • 19
  • 37
  • You may as well just do `UPDATE inv SET state = 2 WHERE state = 0`. I'm pretty sure the OP is selecting the first returned row from the first query and updating that in the second. – lc. May 31 '16 at 07:39
  • Oh sure, that's simple. That's what OP should be doing if I understood his problem right. – hashbrown May 31 '16 at 07:41
  • Yeah but it's probably not, that's the thing. The question is not very clear, but I'm pretty sure he wants *one* record to be updated. – lc. May 31 '16 at 07:45
  • If you add ````LIMIT 1```` to the sub-select it should be fine. – kb. May 31 '16 at 07:49
  • Over time there could accumulate more records with state = 0 . See my edits above. I need the `first` found record with state = 0. Others are outside of interest. So what's the LIMIT for ? – Ben May 31 '16 at 08:14