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?
 
Stephan_SchrandtCommented:
UPDATE TBL_B
set TBL_B.IDNUM =
(SELECT TBL_A.IDNUM
FROM TBL_A
WHERE TBL_B.APPNUM = TBL_A.APPNUM)
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.