Insert into a table where record doesn't already exist 1

[lambic]

Hi,

I have a problem with an insert, basically I've populated a table (say table_a) with records based on one criteria, and have to then insert records into table_a based on another criteria, but only if they don't already exist in table_a. E.g.

insert into table_a (account_no, reference_no, status)
select
account_no = a.account_no
reference_no = a.reference_no
status = 'A'
from table_x a
where
etc

Then I need to insert more records, based on slightly different criteria (which will have status = 'B') but only if there isn't already a record in table_a with the same account_no / reference_no combination.
Can anybody suggest a way of doing that?

Cheers

 

[PHV]

INSERT INTO table_a (account_no, reference_no, status)
SELECT account_no, reference_no, 'B'
FROM table_x
WHERE ...
AND account_no Not In Select(account_no FROM table_a)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JarlH]

Just one minor detail, if table_a.account_no may contain NULL values, this may cause problems.

To make sure nothing unexpected happens rewrite as:
INSERT INTO table_a (account_no, reference_no, status)
SELECT account_no, reference_no, 'B'
FROM table_x
WHERE ...
AND account_no Not In (Select account_no FROM table_a
WHERE account_no IS NOT NULL)

 

[lambic]

Hi,

Thanks for that guys. I didn't explain propertly that what I don't want to insert into the table is a record that has the same account_no & reference_no combination as a previously inserted record. The second insert can pick up the same account_no, but I only want to insert it if the reference_no is different (and the other WHERE criteria are met) to any records already in table_a.

Sorry!

 

[PHV]

INSERT INTO table_a (account_no, reference_no, status)
SELECT account_no, reference_no, 'B'
FROM table_x
WHERE ...
AND account_no Not In Select(account_no FROM table_a WHERE reference_no=table_x.reference_no)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lambic]

Thanks to the both of you - will give it a try

Cheers!

 

[lambic]

Thanks guys - problem sorted now.

Cheers!