Mysql update a row with another row value in same table

mysql

Solution

Use a self-join with the multiple table `UPDATE` syntax:

UPDATE `table` AS t1 JOIN `table` AS t2 ON t2.slNo = 5
SET    t1.Name = t2.Name, t1.Quali = t2.Quali, t1.Exp = t2.Exp
WHERE  t1.slNo = 3

Problem

I have a table. I want to update the 5th row with 10th row values from the same table. For example: ``` SlNo Name Quali Exp 1 x B.E 2 2 y BSC 3 3 Z B.A 1.5 4 A MSC 2 5 B MBA 5 ``` Here i want to update second row with the value of 5th row. Here is my current query: ``` UPDATE table SET Name=(select Name from table where slNo='5'), Quali=(select Quali from table where slNo='5'), Exp=(select Exp from table where slNo='5') where slNo='3'; ``` this is working fine ... but if there are more than 20 columns it becomes laborious to write a query this way, because for each column I have to include another sub-query... is there any other way to write query to update the whole row with all values from the other row in the same table?

Original source