Update table in sql with join

r3nder
r3nder used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
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';
IT Engineer
Distinguished Expert 2017
Commented:
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

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';

Author

Commented:
Thanks man!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial