Insert a value If a match is found in join table

Question

I have a table of ICD9 codes where several of them are not coded properly. I would like to insert "No" into a column in that table if a match can not be found in another table.

So for example, I have a code 292300 and It should be 2923. I want to have something like

SELECT icd9, icd10 from icd9codes, GEM where icd9 = icd10;

And if there is not a match found for the icd9 code then do an INSERT INTO.

Note: GEM table contains icd9 codes and their equivalent mappings for icd10.

Answer 1

If you are inserting a new row into icd9codes, then this is a very simple SELECT query.

insert into icd9codes (icd9)
select icd10
from GEM;

That is the most literal answer to your question. However, I suspect that what you have is existing rows in icd9codes and you want to update a column value on those rows, if their icd9 value exists in icd10 of GEM.

update icd9codes
set the_new_column = 'No'
where exists (
  select 1
  from GEM
  where icd10 = icd9codes.icd9
);

This says, for any row in icd9codes which has a corresponding value in GEM, set its the_new_column value to No.