Insert into table only where record does not already exists, and source table is the same as destination table
Posted on 2003-10-31
Hi I'm trying to insert data into to a table, by "copying" most of the data from the same table
This script works only if the record does not already exist.
INSERT INTO BOM (PART_NO, ISSUE, REV, C_TYPE, COMP_PART_NO, POS)
SELECT '195N2106', ISSUE, REV, C_TYPE, COMP_PART_NO, POS FROM BOM -- The new PART_NO to be created
WHERE PART_NO = '195N2131' -- The old PART_NO to copy from
AND ISSUE = 6
AND REV = 3
If I have to create a new PART_NO in this BOM table, and I know that it should contain the same components as the "sum" of 2 other PART_NO's (all from one, plus all from a second that differs from the first), then I could use the script mentioned above for the first run (copying from the first old PART_NO) but when I run the second time (copying from the second old PART_NO) it will fail because there will be duplicates.
Can anyone tell me how to rewrite this script so it will just skip/ignore the insert statement when a record allready exists..
Thanks in advance