swap records with constraint
845971Mar 9 2011 — edited Mar 9 2011I have a table
ITEM_ID NOT NULL NUMBER(10)
SLOT NOT NULL NUMBER(10)
DATA NOT NULL NUMBER(10)
ITEM_ID is primary key
SLOT has constraint
Slot is the position of the item in an inventory and is between 0-1000.
If i have the records
ID SLOT DATA
1 0 12345
2 1 6789
The way I go about swapping is using a temp slot with a larger value than data can hold.
UPDATE ITEM set slot=1000 where item_id=1;
UPDATE ITEM set slot=0 where item_id=2;
UPDATE ITEM set slot=1 where item_id=1;
There has got to be a better way to do this? like in one sql? any help would be appriciated!
Anders