Results 1 to 3 of 3
Nov 4, 2013, 08:20 #1
- Join Date
- Apr 2005
- 3 Post(s)
- 0 Thread(s)
Auto increment based on two columns?
This might be simple and I'm just having a mental day, but here goes...
I'm trying to accommodate an incremental value in column B, for each unique value of column A. That is, an incremental parcel number for each department:
CREATE TABLE shipments ( deptId BIGINT UNSIGNED, parcelId BIGINT UNSIGNED, PRIMARY KEY(deptId,parcelId) )Code:
deptId parcelId ------ ---------- 1 1 1 2 1 3 2 1 2 2 2 3
I can manually calculate the next available parcel number for a given deptId, when I insert, egCode:
INSERT INTO shipments(deptId,parcelId) SELECT 1,IFNULL(MAX(parcelId),0)+1 FROM shipments WHERE deptId=1
- Maintain an incremental value in column B for a specified value in column A
- Upon insert, return the new value of column B
- Avoid race conditions
Hopefully someone will point out the blindingly obvious solution that I've missed...