I'm not sure how to word this cause I am a little confused at the moment, so bare with me while I attempt to explain, I have a table with the following fields:
OrderLineID, OrderID, OrderLine, and a few other unimportant ones.
OrderLineID is the primary key and is always unique(which isn't a problem), OrderID is a foreign key that isn't unique(also not a problem), and OrderLine is a value that is not unique in the table, but should be unique for any OrderIDs that are the same...so if that didn't make sense, perhaps a picture
OrderLineID, OrderID, OrderLine
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
For all OrderIDs there is a unique OrderLine. I am trying to create an insert statement that gets the max OrderLine value for a specific OrderId so I can increment it, but it's not working so well and I could use a little help. What I have right now is below, I build the sql statement in a program and replace OrderID # with an actual value. I am pretty sure the problem is with the nested select statement, and incrementing the result, but I can't find any examples that do this since my google skills are weak apparently....
INSERT INTO tblOrderLine (OrderID, OrderLine) VALUES
(<OrderID #>, (SELECT MAX(OrderLine)
FROM tblOrderLine WHERE orderID = <same OrderID #>)+1)
any help would be nice.