sreekem bose sreekem bose - 2 months ago 8
SQL Question

insert data from one table to another in sql which already do not exist

I have a table xx_photo in which i have to insert data from xx_people_ph table.

xx_photo :

IMAGE_ID NOT NULL NUMBER(15)
PARENT_ID NOT NULL NUMBER(15)
TABLE_NAME NOT NULL VARCHAR2(30)
IMAGE NOT NULL BLOB
DATA_FLAG VARCHAR2(2)
HIST_DATE DATE


where whenever we insert data into xx_photo the hist_date will be updated to sysdate and data_flag to 'Y'.

xx_people_ph

IMAGE_ID NOT NULL NUMBER(15)
PARENT_ID NOT NULL NUMBER(15)
TABLE_NAME NOT NULL VARCHAR2(30)
IMAGE NOT NULL BLOB


right now i am using :

insert into xx_photo
SELECT IMAGE_ID ,
PARENT_ID ,
Table_Name ,
Image ,
'Y',
Sysdate
FROM Per_Images Img_Tab;

In this statement only , i want to check if the record for same parent_id exists


in xx_photo. If it already exists then the data should not be inserted in this table.

How can i modify this query to insert only those records which already do not exist in the xx_photo table

Answer

Use Not Exists

INSERT INTO xx_photo (Col1,col2,...)
SELECT image_id, 
       parent_id, 
       table_name, 
       image, 
       'Y', 
       sysdate 
FROM   per_images Img_Tab 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   xx_photo x 
                   WHERE  x.parent_id = Img_Tab.parent_id)