1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.

How to select highest value and then create new object with that value +1?

Discussion in 'SQL - Questions and Answers' started by Pureke, Dec 6, 2006.

  1. Pureke Guest

    Dialect: MS SQL
    I'm a real newb on sql and can't find any solution to my problem.
    I'm having a db with a table Item and in that table are quite a few collumns.
    One of them is named IID
    What I want to do is the following:
    Select the highest IID, then add 1 (say the max IID is 50, I want to use 51) and fill in all the collumns.
    I know how to select that highest IID, I know how to fill in all the other values, I just can't figure out how to add 1 to that IID :s

    I have this:
    SELECT *
    FROM Item
    WHERE IID = (SELECT MAX(IID) FROM Item)

    INSERT INTO Item
    (IID, all the rest of the collumns here )
    VALUES ( no idea what will come here, again all the rest of the values here)


    I'm pretty sure I'll need some extra stuff to make it work but again, I can't figure it out

    Thx already
  2. Musclebai Guest

    Dialect: SQL 92
    SQL:

    INSERT INTO Item
    (IID )
    VALUES (SELECT (IID +1)
    FROM Item
    WHERE IID = convert(int,(SELECT MAX(IID ) FROM Item))
    )
  3. Dimitar Guest

    This should work it most dialects:
    SQL:

    insert into item (iid) select max(iid) +1 from item;

    I find it simpler, but it assumes the IID column is defined NOT NULL.
    Here is another attempt, which does not make that assumption:
    SQL:

    insert into item (iid) select max(case when iid is null then -1 else iid end) +1 from item;

    But what if the table is initially empty?
    As the function MAX is applied to an empty set, it will return the null value, which will become the value of the first IID.
    Let us again consider the first version (with the NOT NULL assumption):
    SQL:

    nsert into item (iid) select case when hm is null then 0 else hm end from (select max(iid) + 1 from item ) as t

Share This Page