CODEWITHSUNDEEP
mysqlsql
mark
mark

Reputation: 1809

MySQL: Updating a column with a concatenation of results

I have a column with a string in it, and due to a bug some of the string values are wrong. So I want to fix the strings by taking the original string and concatenating information from a select to it. Ideally it would be something like:

UPDATE table as t
SET t.string = SELECT CONCAT(t.string, 
                             (SELECT fix FROM othertable where id=t.id )
                            );

But of course, I can't do that using concat. I have to say I've not got very far with any other method.
Anyone have an ideas?

Upvotes: 0

Views: 1266

Answers (2)

Andriy M
Andriy M

Reputation: 77737

If the subquery is guaranteed to return just one value, you could simply drop the SELECT keyword before CONCAT:

UPDATE table as t
SET t.string = CONCAT(t.string, 
                      (SELECT fix FROM othertable where id=t.id)
                     );

If the subquery may return more than one value, a quick fix could be to apply GROUP_CONCAT like this:

UPDATE table as t
SET t.string = CONCAT(t.string, 
                      (SELECT GROUP_CONCAT(fix) FROM othertable where id=t.id)
                     );

Upvotes: 1

ericosg
ericosg

Reputation: 4965

try the below:

UPDATE
table as t

INNER JOIN 
othertable as o

ON
t.id = o.id

SET
t.string = CONCAT(t.string, o.fix)

note that in MySQL (as above) it's sort of different than MSSQL which doesn't use the keyword FROM.

Upvotes: 0

Related Questions