如何使用子查询更新特定列的多行 [英] How to update particular column multiple rows with subquery

问题描述

请帮助 mi 与以下 mysql 查询有关，我有一个主表，如下所示:

please help mi relating to following mysql query, I have a table main with like below:

id      phone                phone2 
======================================
1       98998 | 58877
2       98998 | 58877
3       98998

我想通过电话列更新 phone2 并尝试使用以下子查询，但我没有得到想要的结果，如果可能的话，请帮助得到结果:

i want to update phone2 through phone column and trying to used following subquery but i didnt got the desired result please help to got the result if it is possible:

update main a 
set a.phone2 = (SELECT substring_index(b.phone,'|',-1) 
                FROM main b 
where b.phone like '%|%' and where a.id=b.id) 

推荐答案

试试这个:

UPDATE main a 
    INNER JOIN (SELECT id, substring_index(phone,'|',-1) AS phone
                FROM main 
                WHERE phone LIKE '%|%' 
        ) b
        ON a.id = b.id
SET a.phone2 = b.phone;

这篇关于如何使用子查询更新特定列的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！