Update field using data in another table

I have a table called DEC DATA, it has a field named prodcat.  I have another table called CROSS REF and it has a field named productline that matches the field named prodcat from the DEC DATA table. The CROSS REF table also has another field called prodcat which contains the data I want to update/replace the value for prodcat on my DEC DATA TABLE when prodcat is the same as productline.
ector73Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
ector73

try my post at http:#a40528543
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Update [CROSS REF] c Set c.prodcat = (Select d.prodcat  From [DEC DATA] d Where  d.prodcat = c.productline)
0
 
SimonCommented:
From your description:

UPDATE [DEC DATA]  INNER [CROSS REF]  ON [DEC DATA].prodcat=[CROSS REF].productline
SET [DEC DATA].prodcat=[CROSS REF].prodcat

Hmm.. I see that eghteas beat me to it but interpreted your requirement differently. Not sure which table you actually wanted to update.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Mike EghtebasDatabase and Application DeveloperCommented:
I think you meant this:

UPDATE [CROSS REF]  INNER [DEC DATA]  ON [DEC DATA].prodcat=[CROSS REF].productline
SET [CROSS REF].prodcat=[DEC DATA].prodcat
0
 
Rey Obrero (Capricorn1)Commented:
try this query


update [DEC DATA] inner join [CROSS REF]
on [DEC DATA].productline = [CROSS REF].prodcat
set [DEC DATA].prodcat = [CROSS REF].prodcat
0
 
ector73Author Commented:
I am getting a syntax error on Join operation
0
 
ector73Author Commented:
If I use eghtebas solution I get a pop up screen asking for a parameter.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
UPDATE [CROSS REF]  INNER JOIN [DEC DATA]  ON [DEC DATA].prodcat=[CROSS REF].productline
SET [CROSS REF].prodcat=[DEC DATA].prodcat
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.