"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 A (A_STR) KEY (A_STR) VALUES (?)) A) AS A1 CROSS JOIN (SELECT B_ID FROM FINAL TABLE (MERGE INTO B (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 h2-database+unsubscr...@googlegroups.com. To view this discussion visit https://groups.google.com/d/msgid/h2-database/5a377a82-87cc-4555-bbdf-9ff24b31b591n%40googlegroups.com.
