Raj Kumar Boddu Raj Kumar Boddu - 1 year ago 76
MySQL Question

MySQL - Update by using a value from sub query on same table

I have a table 'mytable' and it has following structure and sample data.

+----+------------+--------------------+
| id | name | password |
+----+------------+--------------------+
| 1 | Raj | somepwd |
+----+------------+--------------------+
| 2 | Rao | abcdefg |
+----+------------+--------------------+
| 3 | Uday | |
+----+------------+--------------------+


I want to update Uday's password with Rao's password.
Can anyone help me with MySQL's update query to solve this.

Thanks in advance.

Answer Source

You need to use this query.

update mytable as t1,
(select id,`password` from mytable where name = 'Rao') as t2
set t1.password = t2.password
where t1.id = 3
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download