問題描述
我有一張桌子.我想用同一個(gè)表中的第 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
這里我想用第 5 行的值更新第二行.
Here i want to update second row with the value of 5th row.
這是我當(dāng)前的查詢:
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 列，以這種方式編寫查詢會(huì)變得很費(fèi)力，因?yàn)閷τ诿恳涣校冶仨毎硪粋€(gè)子查詢......還有其他方法可以編寫嗎?查詢以使用同一表中另一行的所有值更新整行?
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?
推薦答案
使用多表的自聯(lián)接 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
這篇關(guān)于Mysql 用同一表中的另一行值更新一行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！