Previous uploaded Images gets vanished will inserting images in table Oracle

Question

I have a stored procedure in which I am inserting or uploading some rows. While inserting, if the same row is inserted a second time, the earlier rows get deleted with other rows. I want to keep those rows while inserting the new one.

This is the query:

IF TBL_INSERT > 0 THEN
BEGIN
    SELECT COUNT(*) INTO V_PANAROMICIMG_COUNT 
    FROM TBL_VSAT_IMAGE_DETAIL 
    WHERE IMG_TYPE = 'Panaromic' AND IMG_ID = TBL_INSERT;
       
    IF V_PANAROMICIMG_COUNT > 0 THEN
    BEGIN        
        DELETE FROM TBL_VSAT_IMAGE_DETAIL 
        WHERE IMG_TYPE = 'Panaromic' AND IMG_ID = TBL_INSERT;  
    END;        
END IF;  
       
IF P_PANORAMIC_IMAGES IS NOT NULL THEN
BEGIN
    FOR PMULTIFIELDS IN (SELECT REGEXP_SUBSTR(P_PANORAMIC_IMAGES,'[^,]+', 1, LEVEL) AS IMAGES FROM DUAL
                                      CONNECT BY REGEXP_SUBSTR(P_PANORAMIC_IMAGES, '[^,]+', 1, LEVEL) IS NOT NULL
                                    )
    LOOP
        INSERT INTO TBL_VSAT_IMAGE_DETAIL (IMG_ID, IMG_NAME, IMG_TYPE, IMG_UPLOADED_DATE, UPLOADED_BY)
        VALUES (TBL_INSERT, PMULTIFIELDS.IMAGES, 'Panaromic', SYSDATE, P_CREATED_BY);
    END LOOP;
END;
       
END IF;

update

Table desc: TBL_VSAT_IMAGE_DETAIL

Name              Null?    Type            
----------------- -------- --------------- 
IMG_ID            NOT NULL NUMBER          
IMG_NAME                   NVARCHAR2(2000) 
IMG_TYPE                   NVARCHAR2(100)  
IMG_UPLOADED_DATE          DATE            
UPLOADED_BY                NVARCHAR2(100)  
ID                NOT NULL NUMBER  

Sample image name is 90.jpg, 120.jpg. So if the table consists image name with 90.jpg it for that ID then it should either update the image name or delete and insert the new image with same Image name

Answer 1

Use a MERGE statement to to either an INSERT or an UPDATE depending on whether the row already exists:

IF P_PANORAMIC_IMAGES IS NOT NULL THEN
  MERGE INTO TBL_VSAT_IMAGE_DETAIL dst
  USING (
    SELECT REGEXP_SUBSTR(P_PANORAMIC_IMAGES,'[^,]+', 1, LEVEL) AS image_name
    FROM   DUAL
    CONNECT BY REGEXP_SUBSTR(P_PANORAMIC_IMAGES, '[^,]+', 1, LEVEL) IS NOT NULL
  ) src
  ON (   dst.img_id = TBL_INSERT
     AND dst.img_name = src.image_name )
  WHEN MATCHED THEN
     UPDATE
     SET img_type          = 'Panoramic',
         img_uploaded_date = SYSDATE,
         uploaded_by       = P_CREATED_BY
  WHEN NOT MATCHED THEN
    INSERT (IMG_ID, IMG_NAME, IMG_TYPE, IMG_UPLOADED_DATE, UPLOADED_BY)
    VALUES (TBL_INSERT, src.image_name, 'Panaromic', SYSDATE, P_CREATED_BY);
END IF;

fiddle

Note: You probably do not want to DELETE beforehand so try removing the first IF statement - however your business logic is not fully explained so there may be a reason why you are doing that.

If you do need the DELETE (I don't think you do) then you do not need to check if rows exist before trying to delete them, just try to delete them and if there are no rows then nothing gets deleted. It can be simplified to:

IF TBL_INSERT > 0 THEN
  DELETE FROM TBL_VSAT_IMAGE_DETAIL 
  WHERE IMG_TYPE = 'Panaromic' AND IMG_ID = TBL_INSERT;  
END IF;