MySQL用同一表中的另一行值更新一行 [英] Mysql update a row with another row value in same table

问题描述

我有一张桌子.我想用同一张表中的第10行值更新第5行.例如:

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.

这是我当前的查询:

    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';

这工作正常...但是，如果有20多个列，则以这种方式编写查询变得很费力，因为对于每一列，我都必须包含另一个子查询...还有其他写方式吗?查询以使用同一表中另一行的所有值来更新整行?

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?

推荐答案

对多表UPDATE语法使用自联接:

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

这篇关于MySQL用同一表中的另一行值更新一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！