SQL - looping through columns, once empty is found, update it

Question

Hi, I started similar thread but as the problem is more difficult, I would like to start from scratch.
Imagine there are 10 inventory slots (in DB they are columns in the user's row). When an users picks an item up, it should be placed into the first empty column (it gets updated). But how to do the looping through the columns (except for IF EXISTS) in the query if I want to have still flexible design? It cannot be solved well by having the items in rows as the order of items matters (each item belongs to particular slot). If the users has 1nd and 3rd slot full, next picked item should go into the 2nd. Hope its more clear now, thanks!

Answer 1

You are breaking 1NF by doing it this way, and that is why you are running into this problem.

I think it can be solved by having items in rows. You can put each one in a row in another table, with another column in that table to specify order.

I'm not sure what Inventoryslot relates to in your example, but the table should look similar to this.

InventorySlots(InventorySlotId, RelatedFieldID, OrderNumber, ItemID)

You can put a UNIQUE index on RelatedFieldID and OrderNumber to prevent two items in the same slot.

Answer 2

Why not just have another table for the inventory items? So you'd have an inventory table and inventory_item table, like this:

----------
inventory
----------
id int
description varchar(100)

---------------
inventory_item
---------------
id  int
inventory_id int (foreign key to inventory table)
sequence_num int (indicates which "slot" the data represents)

So now, you could have a row in inventory, and then you could insert the rows into inventory_item as you get the values. So if you have 3 values, you would just insert the 3 rows into inventory_item. As for what "slot" they go in, the sequence_num field would tell you that.

EDIT
To query for existing inventory_items to find out the next available slot number, you could do something like:

SELECT coalesce(MAX(sequence_num),0) + 1 next_sequence_num
  FROM inventory_item
 WHERE inventory_id = xxx

If no inventory_items exist, this would return 1. If existing items are present, it will give you the next "slot number" to use (I call it sequence_number, but same thing).

Answer 3

I would say you could still use rows. Just add a column "SlotOrder" or somesuch. Then, your query goes something like (warning: psuedo code - not tested at all):

declare @Slot int
set @Slot = 1
if @Slot <= (Select Max(SlotOrder) from Inventory)
   select @Slot = (Select Max(SlotOrder) + 1 from Inventory)
if @Slot >= 10
   Insert Into Inventory(Item, SlotOrder)
   Values(@Item, @Slot)
Else -- Tell the user the inventory is full

Answer 4

The items in a table have no particular order, so order alone cannot be used to distinguish different slots.

However, that may not be important here, since you want to find the first empty slot. Without any ordering on slots, that amounts to finding any empty slot.

This is done like this:

update slots s
set slotValue=newValue
WHERE s.userID = <UserID> AND slotValue IS NULL AND NOT EXISTS 
(SELECT 1 FROM slots s2 WHERE s2.userid=s.userid AND s2.slotValue IS NULL AND s2.slotID<s.slotID)

If you add a slotNr fields to the slots table (numbering a user's slots from 1..10) then the query would look like this:

update slots s
set slotValue=newValue
WHERE s.userID = <UserID> AND slotValue IS NULL AND NOT EXISTS 
(SELECT 1 FROM slots s2 WHERE s2.userid=s.userid AND s2.slotValue IS NULL AND s2.slotNr<s.slotNr)

When you execute the query, the database returns the number of rows affected. If all slots are in use, then the return value will be 0, otherwise 1 if the slot was updated.

You can find out how many free slots there are by writing

select count(*) FROM slots 
WHERE slots.userID=<userID> AND slots.slotValue IS NULL

Although even if this returns free slots, there is no guarantee that when you actually come to change an empty slot value, that there are any free slots - other operations adding slot values for that user might take the last available slot. So, it's best to use the ROW_COUNT variable to determine if a slot was modified or not.