sql - How to update a column based on the id of another column using matching columns -

the title confusing let me explain, have 3 tables think need work with.

• the first table called vendorcertifications , has certid, cert, vendorid columns.
• the second table vendors , has vendorid , vendor columns
• the third table vendorcert , has cert , company columns

what did insert vendorcert vendorcertification , have list of certs , pk certid filled in using identity specification.

what want go , update vendorcertification table vendorid vendors.

i thinking can join vendors vendor column vendorcerts company column.

and use cte update vendorcertification vendorid column.

this have written:

with temptable (    select         vce.company, v.vendor, vce.certification, v.vendorid           vendorcert vce    join        vendors v on v.vendor = vce.company) update vendorcertifications set vendorid = temptable.vendorid temptable.certification = certification 

this did not work, getting "could not bound" error, have tried figure out why giving me that, having no luck. not sure if should doing way, cant think of way using normal update statement because involves(at least thinking involves) 3 tables. appreciated. list of certifications around 300 vendors list around 40.

please try this:

with temptable (company,vendor,certification,vendorid) as( select vce.company,v.vendor,vce.certification,v.vendorid vendorcert vce join vendors v on v.vendor=vce.company)  update vc  set vc.vendorid=tt.vendorid vendorcertifications vc join temptable tt on vc.certification=tt.certification