I am trying to perform INSERT WHERE NOT EXISTS in singlestore using spring jpa native quey but I’m getting Sql grammar exception. I’m using sql5dialect.

Can someone help me with performing the query

Query - INSERT INTO table(col1) VALUES (‘colValue’) WHERE NOT EXISTS (SELECT 1 from table WHERE col2 = ‘coValue’

I have also tried INSERT INTO table(col1) Select (‘colValue’) from dual WHERE NOT EXISTS (SELECT 1 from table WHERE col2 = ‘coValue’

Hi EricB,

This query should work :
INSERT INTO table(col1) select ‘colValue’ from dual WHERE NOT EXISTS (SELECT 1 from table WHERE col2 = ‘coValue’);
Are you able to use it in your code instead of the auto-generated one?

Actually we are using native query in jpa and not auto generated. But even running the insert statement as is did not work
I am using database console from intellij for running the commands

INSERT INTO table(col1, col2, col3) select (‘colValue1’, ‘colValue’, ‘colValue’ ) from dual WHERE NOT EXISTS (SELECT 1 from table t WHERE t.col1 = ‘colValue1’ AND t.col2 = ‘coValue’);

Error - check the manual the corresponds to your MySQL version for the right syntax to use near
‘, colValue, colValue ) from dual WHERE NOT’