Concat two strings in postgreSQL and MySQL

Recently, I have faced with the situation that I had to update one column value with the combination of two strings (one is another column string and the other one is a single word) for entire table. One way is to update each row manually but what if there are millions rows to update?

In my case the quantity was not millions and it was less than 100 but I felt lazy to do and looked for an efficient solution. Before proceeding with the solution, I would like to give a brief explanation about my problem. Basically, in the table that I wanted to apply changes there were two main columns. One is pkid and the other one guid. For unknown reason, guid column was empty surprisingly. But I had a backup for unique value for each row which was pkid. Now I wanted to populate the guid values since guid column is used extensively instead of pkid.0

I wanted to do something for that for empty guids,

guid = ‘TESTING_’ + Pkid

As I mentioned before one way is to update each column manually.

The best approach is to write a stored procedure to that.

And the best approach to do simple search and get familiar with predefined function to make things simpler.

The solution is pretty straight forward and easy as you can see below

BEGIN;
UPDATE guid FROM MyTable SET guid = CONCAT(‘TESTING_’ , pkid) WHERE guid = ‘’;
COMMIT;

Send your idea and information to kasra@madadipouya.com

Leave a Reply