Copy field to another field, different rows, where id = parentID

I'm quite new to mysql and don't know that much, never the less I need to get some things done. :)

bla bla

I'm trying to figure out how to concat a string from one field, to the beginning of another. In the same table, but different rows. Using where id = parentID or such. Check out my tables below, hopefully you'll understand.

My table:

table
id | textcol | extra_textcol | parentID
1  | 'text1' | NULL          | NULL
2  | NULL    | 'extratext2'  | 1
3  | 'text'  | NULL          | NULL

Result I'm trying to get:

table
id | textcol           | extra_textcol | parentID
1  | 'extratext2text1开发者_如何学C' | NULL          | NULL
2  | NULL              | 'extratext2'  | 1
3  | 'text'            | NULL          | NULL

Anyone out there who could help me with this?

EDIT: Forgot to say, the result I want is from an UPDATE :D

SELECT  mc.id, CONCAT(COALESCE(GROUP_CONCAT(mp.extra_textcol ORDER BY id SEPARATOR ''), ''), mc.textcol) AS textcol, mc.extra_textcol, parentID
FROM    mytable mc
LEFT JOIN
        mytable mp
ON      mp.ParentID = mc.id
GROUP BY
        mc.id

To make an update, you'll have to create a temporary table first, since MySQL won't let you use subqueries to the updated table in an update.

CREATE TABLE newtable (id INT NOT NULL PRIMARY KEY, textcol TEXT) ENGINE=Memory
AS
SELECT  mc.id, CONCAT(COALESCE(GROUP_CONCAT(mp.extra_textcol ORDER BY id SEPARATOR ''), ''), mc.textcol) AS textcol, mc.extra_textcol, parentID
FROM    mytable mc
LEFT JOIN
        mytable mp
ON      mp.ParentID = mc.id
GROUP BY
        mc.id;

UPDATE  mytable m
JOIN    newtable n
ON      m.id = n.id
SET     m.textcol = n.textcol

If you have an out-of-memory error on CREATE TABLE, you'll have to use MyISAM instead of Memory storage engine.