ThreeKingz ThreeKingz - 4 months ago 10
MySQL Question

How can I select the next available value in a column taking into account another column in SQL?

I have the following SQL table:

bid btype Name world vi
---|----|------------ |--------|---------
1 | 1 | Business 1 | 0 | 44
2 | 4 | Business 2 | 0 | 55
5 | 5 | Business 3 | 0 | 23
3 | 1 | Business 4 | 1 | 99
4 | 2 | Business 5 | 0 | 12
6 | 3 | Business 6 | 0 | 14
7 | 2 | Business 7 | 1 | 55
8 | 1 | Business 8 | 2 | 66
9 | 2 | Business 9 | 2 | 77
10 | 1 | Business 10 | 3 | 88


What I want is to gradually increase the value in the "world" column according to its "btype", for example every single row starts with a value 0 in the "world column
as it's the first time such value in the "btype" column is inserted, what I want is to check if there's already a "btype" inserted so that the "world" column no longer will assume a value of 0 but 1 and so on...
What I want to achieve is that there can't be two rows sharing the same "btype" with the same "world", the "btype" can be the same but not the "world", it has to be different and I want
it to gradually increase.

How would I approach to do such thing?

Answer

I don't usually do queries like this, so I can't say for certain, but something like this should maybe do the trick.

INSERT INTO theTable(btype, Name, world, vi)
SELECT [val1]
   , [val2]
   , IFNULL((SELECT MAX(world) FROM theTable WHERE btype = [val1]),-1)+1
   , [val3]
;

You might even be able to just include the 3rd select expression in a conventional INSERT...VALUES value list; but like I said, I don't usually do queries like this. (I'm of the apparent minority that likes to check first before inserting; but not as a replacement for an appropriate uniqueness constraint.)