I'm trying to insert rows into a table that has multiple constraints. My statement is failing because SQL is looking at the first constraint field and sees that the value already exists, even though the second constraint is different. An example is VCHRNMBR='xyz' is already in the table with DOCTYPE=1 and I need to add a record to the table for VCHRNMBR='xyz' with DOCTYPE=6. Is there a way to accomplish this without modifying the constraints on the table prior to the insert?
Here's the code:
insert into PM30200
from PM30300 a
where a.VCHRNMBR not in (select VCHRNMBR from PM30200 where DOCTYPE=6) and DOCTYPE=6
All help is appreciated! Thanks!