CODEWITHSUNDEEP
mysqlsqlsql-updateinner-join
cobp
cobp

Reputation: 772

Update of single table using join

I have a select using two tables via a join. I need to update first table using the second table field. Select as follows 

select R.f1, Z.f2,R.f3 
FROM
(select * From Table1 where f2<>'xx' and f3='z') R inner join
(select * From Table2 where f3='xx')             Z 
 ON R.f1⁼Z.f1 and R.f4=Z.f4

How could I update correspond to same select of Table1 (SET R.f2=Z.f2)? 

UPDATE 

(select * From Table1 where f2<>'xx' and f3='z') R inner join
(select * From Table2 where f3='xx')             Z 
 ON R.f1⁼Z.f1 and R.f4=Z.f4
 SET R.f2=Z.f2

When I tried, I get a response that

target table R of the UPDATE is not updatable

Upvotes: 2

Views: 60

Answers (2)

Mureinik
Mureinik

Reputation: 311123

You should perform the update on table1 and join it to the second subquery. The conditions on table1 from the first subquery could be applied in the where clause:

UPDATE table1 r
JOIN   (SELECT * FROM table2 WHERE f3 = 'xx') z  ON r.f1 ⁼ z.f1 AND r.f4 = z.f4
SET    r.f2 = z.f2
WHERE  r.f2 <> 'xx' AND r.f3 = 'z'

Upvotes: 1

amcdermott
amcdermott

Reputation: 1585

You can use a join in update statements using the following....

update t
   set t.Column = value
  from Table t
 inner join OtherTable ot on t.Id = ot.TID
 where somecondition = true.

Upvotes: 1

Related Questions