Coalesce not working in insert statement for a null table

Question

I am trying to use the coalesce function in SQL to avoid getting an error when inserting a row with an auto-incrementing uid into a table that is null. However, the following code is still giving me:

"cannot insert the value null into column 'TABLE_ONE_UID'".

cmdEx.ExecuteNonQuery(
    "INSERT INTO TABLE_ONE 
            (TABLE_ONE_UID, USER_UID, SHT_DATE, 
            C_S_UID, CST_DATE, 
            CET_DATE, S_M, PGS) 
    VALUES ((SELECT MAX(COALESCE(TABLE_ONE_UID, 0)) + 1 
                FROM TABLE_ONE), 
            127, '2009-06-15T13:45:30', 
            0, '2009-06-15T13:45:30','2010-06-15T13:45:30', 
            'TEST DELETE THIS ROW', 0 )");

Possible duplicate of [Why is SQL server throwing this error: Cannot insert the value NULL into column 'id'?](https://stackoverflow.com/questions/10013313/why-is-sql-server-throwing-this-error-cannot-insert-the-value-null-into-column) — Nico Haase, Feb 01 '19 at 08:38

score 1 · Accepted Answer · answered Jan 30 '19 at 12:00

1

The correct way to solve this is with an auto_increment column:

create table PMS_CALC_SCHEDULE (
    PMS_CALC_SCHEDULE_UID int auto_increment primary key,
    . . .
);

If, for some reason, you want to do the calculation yourself, subject your code to race conditions, and have slower inserts, then you need to do the coalesce in the right place:

INSERT INTO PMS_CALC_SCHEDULE (PMS_CALC_SCHEDULE_UID, . . .) 
    SELECT COALESCE(MAX(PMS_CALC_SCHEDULE_UID), 0) + 1, 
           . . .
    FROM PMS_CALC_SCHEDULE ;

answered Jan 30 '19 at 12:00

Gordon Linoff

1,242,037
58
646
786

Thanks, I had actually tried that first option, but I couldn't figure out how to use it within my insert statement. Kept getting a syntax error when trying to add: int auto_increment primary key, next to PMS_CALC_SCHEDULE_UID. How should I use it within the insert column? as the table may or may not already exist, create table is not an option. – Joe Joe Joe Jan 30 '19 at 12:51
@JoeJoeJoe . . . You don't insert it at all. Leave it out of the list of columns and the values. Alternatively, you can include it an pass in `NULL` -- MySQL knows what to do in that case. – Gordon Linoff Jan 30 '19 at 13:01

Darshan Mehta · Answer 2 · 2019-01-30T12:07:36.040

This would happen when your source table doesn't have any row, MAX would return null in this case.

To prevent this, you can use interchange COALESCE and MAX, e.g.:

INSERT INTO PMS_CALC_SCHEDULE 
            (PMS_CALC_SCHEDULE_UID, USER_UID, SCHEDULED_DATE, 
            PMS_CALC_STATUS_UID, CALCULATION_START_DATE, 
            CALCULATION_END_DATE, STATUS_MESSAGE, PROGRESS) 
    VALUES ((SELECT COALESCE(MAX(PMS_CALC_SCHEDULE_UID), 0) + 1 
                FROM PMS_CALC_SCHEDULE), 
            127, '2009-06-15T13:45:30', 
            0, '2009-06-15T13:45:30','2010-06-15T13:45:30', 
            'TEST DELETE THIS ROW', 0 )")

Here's the SQL Fiddle.

score 0 · Answer 3 · answered Jan 30 '19 at 12:00

0

If the field is set to AutoIncrement on the table itself, just leave the field out of your Insert-Statement. The value is added by the database itself.

answered Jan 30 '19 at 12:00

goetterkomplex

61
3

Coalesce not working in insert statement for a null table

3 Answers3