Increment non unique field during SQL insert

Posted by phill on Stack Overflow See other posts from Stack Overflow or by phill
Published on 2010-04-02T05:30:41Z Indexed on 2010/04/02 5:33 UTC
Read the original article Hit count: 314

Filed under:
|
|
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about insert