INSERT INTO table SELECT not working with constant values in H2 DB

Question

Following is the H2DB query

 INSERT INTO userpermission (userid, permissionid, createddt, createdby, updateddt, updatedby)
            SELECT u.userid, p.permissionid, now(), ("1"), now(), ("1") FROM user u, permission p
            WHERE u.email = "csr_admin@example.com";

What I am trying to achieve is to insert a record into userpermission for user with given email address in user table, for each permission in permission table.

Following is the error

 Reason: liquibase.exception.DatabaseException: Column "1" not found; SQL statement:
                                    INSERT INTO userpermission (userid, permissionid, createddt, createdby, updateddt, updatedby)
                                                SELECT u.userid, p.permissionid, now(), ("1"), now(), ("1") FROM user u, permission p
                                                WHERE u.email = "csr_admin@example.com" [42122-193] [Failed SQL: INSERT INTO userpermission (userid, permissionid, createddt, createdby, updateddt, updatedby)
                                                SELECT u.userid, p.permissionid, now(), ("1"), now(), ("1") FROM user u, permission p
                                                WHERE u.email = "csr_admin@example.com"]

The same query is working in MySQL.

Not sure about H2, but in all RDMS, the constants are declared with single quotes. Also braces are unnecessary. Replace `("1")` with `'1'`. — Utsav, May 15 '17 at 03:38
Replace double quotes with single quotes as well. Just run the select statement first. Also you are doing a cross join by not giving join condition. Is that intentional ? — Utsav, May 15 '17 at 03:41
You are correct. I had to replace the where clause with single quotes. MySQL would accept it but not H2. Thanks! — TechCrunch, May 15 '17 at 03:42
Happy to help. I put the comment as answer as well. Accept it so the question could be closed. Thanks! — Utsav, May 15 '17 at 03:52

score 2 · Accepted Answer · answered May 15 '17 at 03:51

2

As mentioned in the comments,

Use single quotes for sting constants.
Do not use braces with constants in select clause.
Check the join condition. Without matching 2 tables based on common column, you are doing a Cartesian product

answered May 15 '17 at 03:51

Utsav

7,914
2
17
38

1

Only the first point is the cause of the error. Braces is optional. 3 is intentional in my case. I am looking to insert values into cross product table for testing which are usually entered manually. – TechCrunch May 15 '17 at 03:54
Ok. Thanks for clearing it up as the cartesian product was bugging me. – Utsav May 15 '17 at 03:56

score 0 · Answer 2 · answered Sep 03 '21 at 19:54

I encountered a similar issue. I want to insert some default value into my H2 when my springboot app first runs. I had the schema.sql and data.sql all correct.

However, It still won't load the sample data into the H2 Schema table when I do select *. after looking around for a hour. I decided to look into the application.properties file. I changed

spring.jpa.hibernate.ddl-auto=create-drop

to spring.jpa.hibernate.ddl-auto=none

or spring.jpa.hibernate.ddl-auto=update

both of them work. just in case you wondering why both none and update works, here is why

INSERT INTO table SELECT not working with constant values in H2 DB

2 Answers2