2 linked inserts in one statement

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • posh70
    New Member
    • Apr 2009
    • 1

    2 linked inserts in one statement

    I'd like to insert record into GROUP table, get autogenerated value group_id and then insert record into another table, USER, with this group_id as a foreign key.
    Code below works perfect, but since this possibility is not documented, I'm slightly aware. Would you recommend this way or do you know a better way?
    DB2 v8.1. If you are going to test this query in Control Center be aware that you need to run commit after this.
    I've put DDL below. Thanks a lot !



    Code:
    with tmp1 as 
    (select group_id
    from FINAL TABLE (INSERT INTO GROUP (group_name)
         VALUES ('test'))),
    tmp2 as 
    (select count(*) as num
    from FINAL TABLE (INSERT INTO USER (group_id, user_id)
         select group_id, 'test_user' from tmp1)
    )
    select num from tmp2;

    DDL:
    Code:
    CREATE TABLE "GROUP" (
      "GROUP_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (  
    		    START WITH +1  
    		    INCREMENT BY +1  
    		    MINVALUE +1  
    		    MAXVALUE +2147483647  
    		    NO CYCLE  
    		    NO CACHE  
    		    NO ORDER ) , 
       "GROUP_NAME" VARCHAR(30) NOT NULL);
    
    ALTER TABLE "GROUP" 
    	ADD PRIMARY KEY
    		("GROUP_ID");
       
    
    CREATE TABLE "USER" (
      "GROUP_ID" INTEGER NOT NULL , 
      "USER_ID" VARCHAR(64) NOT NULL); 
    
    
    ALTER TABLE "USER" 
      ADD CONSTRAINT "GROUP_ID_FK" FOREIGN KEY
    		("GROUP_ID")
    	REFERENCES "GROUP"
    		("GROUP_ID")
    	ON DELETE CASCADE
    	ON UPDATE RESTRICT
    	ENFORCED
    	ENABLE QUERY OPTIMIZATION;
Working...