CODEWITHSUNDEEP
mysql
Sergio
Sergio

Reputation: 1239

Mysql update values

I'm using two tables in the database. The first table looks like this:

   id    |   msg_id   |     user   |   date   
-------------------------------------------------
   01    |    122     |    user 1  | 2011-04-01
   02    |    453     |    user 2  | 2011-04-03
   03    |    124     |    user 3  | 2011-04-05

And the second table looks like this:

       id    |   msg_id   |     status   | 
   ----------------------------------------
       01    |      0     |       1      | 
       02    |      0     |       1      | 
       03    |     124    |       1      |

I want to update all the rows with the "0" value ("msg_id" column) in the second table based on msg_id records from the first table. Is it possible to do it with a single query?

The result should look like:

       id    |   msg_id   |     status   | 
   ----------------------------------------
       01    |    122     |       1      | 
       02    |    453     |       1      | 
       03    |    124     |       1      |

Upvotes: 5

Views: 1083

Answers (5)

Senthil
Senthil

Reputation: 5804

UPDATE table1 AS t1, table2 AS t2
SET t2.msg_id=t1.msg_id
WHERE t1.id = t2.id and t2.msg_id = 0;

Upvotes: 1

Maulik Vora
Maulik Vora

Reputation: 2584

UPDATE secondTable as st , firstTable as ft set st.msg_id = ft. msg_id WHERE ft.id = st.id
and st.msg_id = 0;

Upvotes: 0

xkeshav
xkeshav

Reputation: 54016

TRY

UPDATE table2 t2  SET msg_id = (SELECT msg_id 
  FROM table1 t1 where t2.id = t1.id AND t2.msg_id=0)

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425311

UPDATE  table2 t2
JOIN    table1 t1
ON      t2.id = t2.id
SET     t2.msg_id = t1.msg_id
WHERE   t2.msg_id = 0

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

UPDATE secondTable 
SET msg_id = (SELECT msg_id FROM firstTable WHERE firstTable.msg_id = secondTable.msg_id)
WHERE msg_id = 0;

Upvotes: 0

Related Questions