CODEWITHSUNDEEP
mysqlsql
stackedandflowed
stackedandflowed

Reputation: 61

Mysql Update based on existence in other table

I'm trying to figure out how to mass update a mysql table based on if a value exists in a column in another table.

e.g. pseudo code:

if Table1.`col`=Table2.`col` then
  Update Table1.`status`=1

or

if table2.`col` exists in table1.`col`
  Update Table1.`status`=1

What's the best way to achieve this?

Upvotes: 4

Views: 5626

Answers (3)

rossno
rossno

Reputation: 1

update t_checkout A
INNER JOIN t_target B on A.Media_ID = B.Media_ID
set A.status = 'R'
where A.Media_ID = 45
and exists (select * from t_target where B.Media_ID = 45 and status = 'R');

The 45 is hard coded here, but the value actually comes from a php parameter.

Upvotes: -1

Sagotharan
Sagotharan

Reputation: 2626

Table 1 

col  | status 
-------------

jaga |   0

kala |   0

Table 2

col   | status 
--------------

jaga  |   1

latha |   0

If Table1.col=Table2.col // So this point is fullfill jaga record. then Update Table1.status=1 // So Table 1 jaga row status want to Update in 1.

Is I am Correct?.

Then Try 

UPDATE Table1 AS t1, Table2 AS t2 SET t1.col = 1 WHERE t1.col  = t2.col

Happy Codings,

Upvotes: 1

Devart
Devart

Reputation: 122032

Try this one -

UPDATE table1 t1
  JOIN table2 t2
    ON t1.col = t2.col
SET t1.status = 1;

Upvotes: 9

Related Questions