Click here to Skip to main content
15,886,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Im new with SQL, and I want to knw how can I add a SQL in this structure to check if the row already exists before insert, Im tried this way, but I think its not the best.


DECLARE
    v_project_id   NUMBER;
    v_group_name   VARCHAR2(100);
    v_user_id      NUMBER;
    v_group_id     NUMBER;
    BEGIN
    FOR project IN (
                SELECT project_id
                FROM JSON_TABLE(:p_request,
                                        '$' COLUMNS(NESTED PATH '$.projects[*]'
                                                    COLUMNS(
                                                            project_id  VARCHAR2(100) PATH '$.project.id'
                                                            )
                                                    )
                                )
                )
    LOOP
        v_project_id  := project.project_id;
        v_group_name := json_value (:p_request,'$.group_name');
        v_user_id := json_value (:p_request,'$.user_id');
        v_group_id := s_project_group.NEXTVAL;
        dbms_output.put_line(v_group_id||' - '||v_group_name||' - '||v_user_id||' - '||v_project_id);
--------- HERE, I WANT TO CHECK IF THE NEW ROW ALREADY EXISTS BEFORE INSERT, IM TRIED THIS WAY, BUT I THINK ITS NOT THE BEST------------    
        (select case 
            when NOT exists (select 1 FROM APP_PROJECT_GROUP WHERE GROUP_NAME = :v_group_name AND USER_ID = :v_user_id) 
            INSERT INTO APP_PROJECT_GROUP (GROUP_ID, GROUP_NAME, PROJECT_ID, USER_ID) VALUES (v_group_id, v_group_name, v_project_id, v_user_i)
            );
----------------------------------------------------------------------------------------------------------------------------------------        
        COMMIT;
    END LOOP;
END;


What I have tried:

TRIED THIS WAY, BUT I THINK ITS NOT THE BEST------------    
        (select case 
            when NOT exists (select 1 FROM APP_PROJECT_GROUP WHERE GROUP_NAME = :v_group_name AND USER_ID = :v_user_id) 
            INSERT INTO APP_PROJECT_GROUP (GROUP_ID, GROUP_NAME, PROJECT_ID, USER_ID) VALUES (v_group_id, v_group_name, v_project_id, v_user_i)
            );
Posted
Updated 4-May-22 5:28am
Comments
[no name] 27-Apr-22 12:30pm    
Generate a temporary dataset; then check the inserts; instead of checking while looping.

1 solution

SQL best way I can think of is add it like this:
SQL
IF NOT EXISTS (		
						
				select 1 FROM APP_PROJECT_GROUP WHERE GROUP_NAME = :v_group_name AND USER_ID = :v_user_id
			  )
  BEGIN 

 INSERT INTO APP_PROJECT_GROUP (GROUP_ID, GROUP_NAME, PROJECT_ID, USER_ID) VALUES (v_group_id, v_group_name, v_project_id, v_user_i)

	  END
END
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900