appending colA to colB

[mameha1977]

I want use a MySQL command to append the value of colA to colB:

Original Data:

colA | colB
-------------
a11 | john
b32 | bob
c44 | sally
d89 | mike

Target Data:

colA | colB
-------------
a11 | john (a11)
b32 | bob (b32)
c44 | sally (c44)
d89 | mike (d89)

Is there a command to do that?

[Nedals]

upadate tablename set colB=CONCAT(colB,'(',colA,')')

[mameha1977]

thats the one - thanks.

[mameha1977]

I now need to make it more complex as follows:

UPDATE issue
SET SUMMARY=(SUMMARY, ' (', (SELECT STRINGVALUE FROM customfieldvalue c WHERE ISSUE=72012), ')')
WHERE (key = '21109');

Basically I need to CONCAT and also add results from another query. It doesnt work as written above.

[chazzy]

you missed the concat function in yoru definition.

[mameha1977]

Sorry, I'll repost the exact query I am trying to use:


UPDATE issue
SET SUMMARY=CONCAT(SUMMARY, ' (', (SELECT STRINGVALUE FROM customfieldvalue WHERE ISSUE=72012), ')')
WHERE (
(key = '21109')
);


I get a syntax error on 'line 2' which has the CONCAT bit.

If I replace the SELECT query with a text string it works fine.

[chazzy]

I'm not sure what DBMS you're using, but I'm not sure that all versions of concat support the syntax you're trying to use (variable arguments).

[mameha1977]

Is there some way to do it by combining two queries (UNION?) instead?

[mameha1977]

Strange, variable arguments seem to work when I test the following query on my localhost MySQL:

UPDATE country SET name = CONCAT( name, '(', (
SELECT country
FROM contact
WHERE contact_id =1
), ')' )
WHERE id =5;

I thought it may be an innodb/myisam thing but the query works on both types of tables. The only different I can see is that the version of Mysql is different (localhost = 5.0, live = 5.1).

[mameha1977]

fixed. the problem was I had a ";" character at the end of the select query :(