replacing vales in one table with values from another table using multiple criteria

Question

I have two tables, one called accajd, which a screenshot of is shown below, which has the old and new values in it. and one called jobcost with about 15 fields, 3 of which need to be updated with the data from the accadj table (accno, jno, and saccno). What i need to do is everywhere in job cost that the data from the accno, jno, and saccno fields matches the accnoold, jnoold, and saccnoold from accadj, i need to replace the data in jobcost with the values from accnonew, jnonew, and saccnonew from accadj.

update jobcost 
set accno =(SELECT accadj.[accnonew] FROM accadj 
   WHERE accadj.id = jobcost.[jno] 
  AND accadj.[saccnoold] = jobcost.[saccno]),
jno =(SELECT accadj.[accnonew] FROM accadj 
   WHERE accadj.id = jobcost.[jno] 
  AND accadj.[saccnoold] = jobcost.[saccno]),
saccno =(SELECT accadj.[accnonew] FROM accadj 
   WHERE accadj.id = jobcost.[jno] 
  AND accadj.[saccnoold] = jobcost.[saccno])

this is all I could come up with as far as writing a query goes for this task, and its nowhere close to working. the jobcost table has about 15,000 entries, so this code would only end up replacing about 100 of them

Answer 1

Check Below code it will work for sure ,

Update jc
set jc.accno = ac.accnonew,jc.jno =ac.accnonew,jc.saccno =ac.saccnonew
from jobcost jc join accadj ac with(nolock)
on jc.accno  = ac.accnoold
and jc.jno  = ac.jnoold
and jc.saccno =ac.saccnoold

Thanks