Mysql: how to get next pk in INSERT and ISNULL(), NVL(), IFNULL() and COALESCE() Functions

Let’s say the pk field is not auto increased and we want to insert a new record with the max(pk)+1 in the table as the value of pk.

If hardcode the pk, the sql statement is like this:

INSERT INTO TSETTINGDEFINITION (UIDPK,PATH,DEFAULT_VALUE) VALUES (1,'path1','value1');

We want replace “1” with max(UIDPK)+1 from the same table, but mysql does not allow you to do that. You will get the error

You can't specify target table 'TSETTINGDEFINITION' for update in FROM clause.

An alternative way to do that:

INSERT INTO TSETTINGDEFINITION (UIDPK,PATH,DEFAULT_VALUE)
  SELECT (MAX(UIDPK)+1), 'path1', 'value1' FROM TSETTINGDEFINITION;

But the max(UIDPK) may return null to you, We expect it to be 1 if the MAX(UIDPK) is null. Different databases provide different functions.

MSSQL ==> ISNULL(exp1, exp2)

ORACLE ==> NVL(exp1, exp2)

MYSQL ==> IFNULL(exp1, exp2) or COALESCE(exp1,exp2)

They are all similar, if exp1 is null , then return exp2.

Then the sql statement is like

INSERT INTO TSETTINGDEFINITION (UIDPK,PATH,DEFAULT_VALUE)
  SELECT IFNULL(MAX(UIDPK)+1, 1), 'path1', 'value1' FROM TSETTINGDEFINITION;

The example above only works when the select returns one row.

If the select statement returns multiple rows, the sql below works.

insert into truleparameter
select m.maxId+(@rownum:=@rownum+1), 'couponPrefix', 'AutoCoupon',null, a.RULE_ELEMENT_UID, null
from (SELECT @rownum:=0) r, (select max(uidpk) as maxid from truleparameter) m,  truleparameter a
where param_key='ruleCode';