How to insert a value based on a sub query?

  • 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

  • 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.

  • You could also try...

    INSERT INTO Items ([PartNum], [ItemOrder])

    SELECT @Part_Num, MAX(ItemOrder)

    FROM TableX

    WHERE TableX.Part_Num = @Part_Num

  • 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.

  • 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