Update table in sql with join

I want to update a table with a join - why cant I do this?
UPDATE temp_lkltn 
JOIN   job AS j 
ON     temp_lkltn.lastoflocation = j.jobid 
SET    temp_lkltn.company = j.company, 
       temp_lkltn.rig = j.rig, 
       temp_lkltn.rignumber = j.rignumber, 
       temp_lkltn.wellname = j.wellname, 
       temp_lkltn.city = j.city, 
       temp_lkltn.state = j.state 
WHERE  temp_lkltn.lastoflocation = j.jobid 
AND    j.company <> 'Test Company';UPDATE temp_lkltn 

Open in new window

LVL 6
r3nderAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just add a FROM clause with the same table and INNER JOIN with the table you want to. All after the SET clause:
UPDATE temp_lkltn 
SET    temp_lkltn.company = j.company, 
       temp_lkltn.rig = j.rig, 
       temp_lkltn.rignumber = j.rignumber, 
       temp_lkltn.wellname = j.wellname, 
       temp_lkltn.city = j.city, 
       temp_lkltn.state = j.state 
FROM  temp_lkltn
INNER JOIN   job AS j ON     temp_lkltn.lastoflocation = j.jobid 
WHERE  j.company <> 'Test Company'

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE temp_lkltn
SET    temp_lkltn.company = j.company,
       temp_lkltn.rig = j.rig,
       temp_lkltn.rignumber = j.rignumber,
       temp_lkltn.wellname = j.wellname,
       temp_lkltn.city = j.city,
       temp_lkltn.state = j.state
FROM temp_lkltn
JOIN   job AS j
ON     temp_lkltn.lastoflocation = j.jobid
AND    j.company <> 'Test Company';
0
 
Ramachandar NCommented:
UPDATE temp_lkltn
SET    company = j.company,
       rig = j.rig,
       rignumber = j.rignumber,
       wellname = j.wellname,
       city = j.city,
       state = j.state
FROM
      temp_lkltn temp_lkltn,
      job AS j
WHERE
      temp_lkltn.lastoflocation = j.jobid
AND j.company <> 'Test Company';
0
 
r3nderAuthor Commented:
Thanks man!
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.