Search code examples
sqlinsertclouderaimpala

How to solve impala insert with select query error?

i want to insert a row to service_log table with max+1 id value.

insert into bi_dev.service_logs values (select nvl(max(id),0) + 1 as count from bi_dev.service_logs , 1, now(), 0, "Error")

But this gives me the following error ;

AnalysisException: Syntax error in line 29:undefined: ..._dev.service_logs values (select nvl(max(id),0) + 1 as... ^ Encountered: SELECT Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error.

How can i solve this problem?

Solution

  • This should work with most SQL dialects:

    insert into bi_dev.service_logs 
select nvl(max(id),0) + 1 as count,
      , 1
      , now()
      , 0
      , "Error"
 from bi_dev.service_logs