Select Values from one table and add to another table based on primary key

What I'm looking to do is the equivalent of a Vlookup in excel except I'm working with tables in an Oracle DB.

Tables look like this:

TBL_A
APPNUM      IDNUM
115697         1113
115872         1256
113457         1489
118965         2578

TBL_B
APPNUM      IDNUM
115697        
115872        
113457        
118965        

I would like to move the "IDNUM" values from TBL_A to TBL_B but the need to match "APPNUM".
danmcd0913Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Stephan_SchrandtCommented:
UPDATE TBL_B
set TBL_B.IDNUM =
(SELECT TBL_A.IDNUM
FROM TBL_A
WHERE TBL_B.APPNUM = TBL_A.APPNUM)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
danmcd0913Author Commented:
I haven't gotten an error yet but it's taking a really long time for this update to run (at 4 minutes so far)
0
Cenjoy100Commented:
Try this

UPDATE TBL_B
set TBL_B.IDNUM = TBL_A.IDNUM
From TBL_A.IDNUM
inner join TBL_A
on TBL_B.APPNUM = TBL_A.APPNUM
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Cenjoy100Commented:
Correction

UPDATE TBL_B
set TBL_B.IDNUM = TBL_A.IDNUM
From TBL_A
inner join TBL_B
on TBL_B.APPNUM = TBL_A.APPNUM
0
danmcd0913Author Commented:
So I made up the table names before because I thought it would be easier but here's the actual code i'm trying to run based on your last comment

UPDATE LIS_MASTER_IN
SET LIS_MASTER_IN.APPCNUM = DPM_LIS_IN.APPCNUM
FROM DPM_LIS_IN
INNER JOIN LIS_MASTER_IN
ON LIS_MASTER_IN.LIS_PART_ID_NUM = DPM_LIS_IN.LIS_PART_ID_NUM

When I try to run this I'm getting an 'ORA-00933: SQL command not properly ended' error and the cursor is being placed at the "FROM" portion of the syntax.

0
Stephan_SchrandtCommented:
Inner join is not supported in oracle update. This is MSSQL syntax. How many rows are there in the tables? Do you have indexes on both appnums?
0
danmcd0913Author Commented:
The syntax from your first comment combined with adding an index to both tables (based on your last comment) worked perfectly.  Thanks for your time.
0
Stephan_SchrandtCommented:
I just tested my initial suggestion and it works. The main reason why some oracle procedures take so long are missing indexes. Please have a look at the appnum fields
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.