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 particular, I always seem to have to loop through a table to perform some operation on every record.


Any ideas? Thanks.