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

Question

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 1

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