How to update particular column multiple rows with subquery

Question

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

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)

It seems that you have two numbers in coloumn phone seperated by "|". Are you trying to seperate two numbers, keeping one in phone and other in phone2. Editing with sample/example will help in understanding question. — jsist
– jsist, Commented Aug 6, 2012 at 11:17

Omesh · Accepted Answer · 2012-08-06 11:23:29Z

1

try this:

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;

edited Aug 6, 2012 at 11:23

answered Aug 6, 2012 at 11:15

Omesh

29.4k6 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update particular column multiple rows with subquery

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related