Probleme with id increment

Question

hello, when i do this request i have an error

INSERT INTO FR_METIERPUBLI(
D_NIDMTR,
D_NIDPUBLI
)
VALUES (
'SELECT MAX( D_NIDMTR ) FROM FR_METIERPUBLI + 1', 1000

i want to increment my id

Answer 1

without knowing the database, this is just a guess, but try this:

INSERT INTO FR_METIERPUBLI
        (D_NIDMTR,D_NIDPUBLI)
    SELECT 
        MAX( D_NIDMTR )+ 1, 1000
        FROM FR_METIERPUBLI

for SQL Server, try protecting against no rows existing by using this:

INSERT INTO FR_METIERPUBLI
        (D_NIDMTR,D_NIDPUBLI)
    SELECT 
        ISNULL(MAX(D_NIDMTR),0)+ 1, 1000
        FROM FR_METIERPUBLI

Answer 2

try

INSERT INTO FR_METIERPUBLI(
D_NIDMTR,
D_NIDPUBLI)
SELECT MAX( D_NIDMTR ) +1, 1000 FROM FR_METIERPUBLI

However..be very careful with this..if 2 operations do this at the same time you will get a duplicate

you could do (on SQL Server at least) wrap it in a transaction and specify these locks

 INSERT INTO FR_METIERPUBLI(
    D_NIDMTR,
    D_NIDPUBLI)
    SELECT MAX( D_NIDMTR ) +1, 1000 FROM FR_METIERPUBLI with (UPDLOCK, HOLDLOCK)

Why don't you use a sequence or identity?