April 10, 2009 at 12:50 pm
Hello,
I have a simple Insert SP.
ALTER PROCEDURE [dbo].[usp_InsItem]
@Part_Num varchar(50)=NULL
AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO Items
( [PartNum], [ItemOrder] )
VALUES( @Part_Num)
And have a simple two column table ( PartNum, ItemOrder)
Now in my SP I want to Calculate the Highest ItemOrder which the PartNum has in the table and then insert that to the new row.
So basically lets say I have two entries of a PartNum - 12345, so when I insert another PartNum in the table I want to Insert ItemOrder 3. How can I calculate the Highest ItemOrder for that PartNum and then insert the next one?
Thanks
April 10, 2009 at 1:16 pm
You can write the insert statement like
INSERT INTO myTable1([Col1], [Col2])
SELECT [Col1], [Col2]
FROM myTable2
WHERE [Col1] IN (SELECT MAX([Col1] FROM myTable2)
Does this solves your need.
April 10, 2009 at 1:37 pm
You could also try...
INSERT INTO Items ([PartNum], [ItemOrder])
SELECT @Part_Num, MAX(ItemOrder)
FROM TableX
WHERE TableX.Part_Num = @Part_Num
April 10, 2009 at 1:53 pm
ALTER PROCEDURE [dbo].[usp_InsItem]
@Part_Num varchar(50)=NULL
AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO Items
( [PartNum], [ItemOrder] )
VALUES( @Part_Num,
,(SELECT MAX(ItemOrder)
FROM Items
WHERE Part_Num =@Part_Num)
)
I am getting the Error if I do the above:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
April 10, 2009 at 1:57 pm
Don't make it a subquery. There is no need. Just select the max value for the colum in a standard select and insert the variable value for the other column.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply