CODEWITHSUNDEEP
sqloracle-databasejoinsql-updateoracle-sqldeveloper
user1699121
user1699121

Reputation: 29

Update table with joining another table

I want to set field f1 in table t1 with the value 122 if field f2 in table t2 has value 134. Suppose field f34 is the key between table t1 and t2.
How would I write the query for this in SQL Developer?

Upvotes: 0

Views: 1358

Answers (3)

Himanshu
Himanshu

Reputation: 32612

You can try this one (for Oracle):

UPDATE ( SELECT t1.f1, t2.f2
           FROM t1 
           JOIN t2
             ON t1.f34 = t2.f34
          WHERE t2.f2 = 134
       )
SET f1 = 122;

See this SQLFiddle

For other RDBMS, try update with join

For SQL Server:

UPDATE t1 
SET    f1 = 122
FROM   t1 
       JOIN t2  
         ON t1.f34 = t2.f34
WHERE  t2.f2 = 134

See this SQLFiddle

For MySQL

UPDATE t1 temp1
  JOIN t2 temp2
    ON temp1.f34 = temp2.f34
SET    temp1.F1 = 122
WHERE  temp2.f2 = 134;

See this SQLFiddle

Upvotes: 3

manurajhada
manurajhada

Reputation: 5380

update t1 a,t2 b set a.f1='122' where a.f34=b.f34 and b.f2=134;

Upvotes: 0

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

update t1
set f1 = 122
where exists (select 1 from t2 where f34 = t1.f34 and f2 = 134)

Upvotes: 1

Related Questions