[RESOLVED] how to merge col1 and col2 into col 3 ?

how would I write an UPDATE query so that it will select col1 and col2, concatenate the result and insert into col3 ?

thanks

... it would need to be something like

UPDATE `Person` SET `new_address` = (select `address` + `town` as 'Combined' from Person)

but that just throws up an error

#1093 - You can't specify target table 'Person' for update in FROM clause

so my syntax obviously is well off

what would be really coool would be if I could do

UPDATE `Person` SET `new_address` = (select `address` + ', ' + `town` as 'Combined' from Person)

but maybe i'd have to add the comma into the original address column before concatenating the two cols

any ideas ?

UPDATE tablename
SET col3 = concat(col1, col2)
where something=something

To add comma between concatenated columns you can use this,

concat(col1, ',', col2)

excellent !

in the end I found I could also add in the comma :

UPDATE Person SET `new_address` =  concat( `address` , ', ' , `town` )

thanks 🙂