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.

Something like: `update table1 left join table2 on table1.col = table2.col where table2.col is null set table1.other_col = 'ZZZ'` — juergen d, Oct 20 '15 at 18:50

score 0 · Answer 1 · answered Oct 20 '15 at 18:52

0

We need your database schema, in the meantime, heres a shot in the dark. Back up your table first.

UPDATE icd9codes
LEFT JOIN GEM ON
icd9codes.icd9 = GEM.icd9
SET icd9codes.icd9 = COALESCE(GEM.icd10,'No')

answered Oct 20 '15 at 18:52

Paul Stanley

4,018
6
35
56

score 0 · Accepted Answer · answered Oct 20 '15 at 18:52

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.

you were on the money with the update command. I should have stated the question better. All I was after was to find the codes that had no match in another table so I could fix the formatting so I could map them to the newer codes. Thank you all for the help. — grath, Oct 21 '15 at 14:26

Insert a value If a match is found in join table

2 Answers2