I"m using H2 Version 2.3.232 The database consists of 3 tables.
Table A CREATE TABLE A (A_ID INTEGER AUTO_INCREMENT, A_STR VARCHAR(50), PRIMARY KEY (A_ID)) Table B CREATE TABLE B (B_ID INTEGER AUTO_INCREMENT, B_STR VARCHAR(50), PRIMARY KEY (B_ID)) Table AB - Maps a Many-to-Many relationship between A and B CREATE TABLE AB (A_ID INTEGER, B_ID INTEGER, FOREIGN_KEY (A_ID) REFERENCES A ON DELETE CASCADE, FOREIGN_KEY (B_ID) REFERENCES B ON DELETE CASCADE, PRIMARY KEY (A_ID, B_ID)) I only want to keep 1 copy of each A_STR and B_STR in each of the respective tables. I also want to do a batch update. Since the primary keys are auto generated, I created the following MERGE to do UPSERTS on A and B MERGE INTO AB (A_ID, B_ID) SELECT A1.A_ID, B1.B_ID FROM (SELECT A_ID FROM FINAL TABLE (MERGE INTO ENTITY_1 (A_STR) KEY (A_STR) VALUES (?)) A) AS A1 CROSS JOIN (SELECT B_ID FROM FINAL TABLE (MERGE INTO BLOCK_1 (B_STR) KEY (B_STR) VALUES (?)) B) AS B1 On a clean database, when I execute the query with the parameters "ABC" and "XYZ"... A.A_ID is 1 A.A_STR is "ABC" AB.A_ID is 1 AB.B_ID is NULL, instead of 1 B.B_ID is is NULL, instead of 1 B.B_STR is NULL instead of "XYZ" Is this a bug? Why is the UPSERT for B adding nulls? -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/h2-database/bf11e725-bee8-491a-bf22-f4a58b0f188en%40googlegroups.com.
