AnsweredAssumed Answered

Query Design Question

Question asked by lkeyes on Nov 30, 2012
Latest reply on Nov 30, 2012 by beverly

Hi.... a little embarrassed here that I don't know the answer to this off the top of my head.

 

I've got a table full of physician names. I have a second table full of physician names that include two extra fields, NPI code, and their practice. I need to assign the NPI and Practice to any of the physicians in the first table that I can, using the second table as a lookup.

 

 

I've got two tables:

 

Last, First, NPI Practice

Jones Jeremy <empty> <empty>

 

Last, First, NPI Practice

Jones, Jeremy 1020323, "Community Health"

 

 

I want to move through every record in the first table; grab the name and then do a search on the second table for the name. if found, I want to insert teh NPI# and Practice into the record into the first table

 

I think the effect is something like a VLOOKUP in Excel... (which, if I get really stuck....I may have to fall back to...)

 

or as a nexted SQL statement, something like:

 

INSERT INTO table1.NPI, table1.practice VALUES(table2.NPI, table2.practice)

WHERE (SELECT * FROM table2 WHERE table2.last=table1.last)

 

 

Even though this is a one-off, I'd be happy to see it scripted....in particular, I always seem to have to loop through a table to perform some operation on every record.

 

Any ideas? Thanks.

Outcomes