Search code examples
sqlsnowflake-cloud-data-platformsnowflake-schema

Insert statement - select within value error

I am trying to run something like this:

INSERT INTO TABLE_A
(RULE_ID, SYSTEM_KEY, FIELD, FIELD_VALUE, SOURCE_FIELD_VALUE_DESCRIPTION)
values ((SELECT TO_NUMBER(max(RULE_ID) + 1) as RULE_ID from TABLE_A),6,'CARS',22,'TOYOTA')

however, I keep getting an error:

SQL compilation error: Invalid expression [(SELECT CAST((CAST(TO_NUMBER(MAX(TABLE_A.RULE_ID), 18, 5) AS NUMBER(18,5))) + 1 AS NUMBER(38,0)) AS "RULE_ID" FROM DB.SCHEMA.TABLE_A AS TABLE_A)] in VALUES clause

I don't see any erorrs in my query though, do you know why this is happening?

Solution

  • To fix it use simple INSERT INTO SELECT:

    INSERT INTO TABLE_A
(RULE_ID, SYSTEM_KEY, FIELD, FIELD_VALUE, SOURCE_FIELD_VALUE_DESCRIPTION)
SELECT (SELECT TO_NUMBER(max(RULE_ID) + 1) as RULE_ID from TABLE_A),6,'CARS',22,'TOYOTA';

    Second: MAX(...) + 1 is not safe with concurrent queries. A much safer approach is usage of SEQUENCE or IDENTITY column.