Update column B based on Column A for all rows in table
I need to insert hash value into column b based on value of column a, but I need to do this for every row in table.
I always get this error no matter what I tried:
ERROR: more than one row returned by a subquery used as an expression
I have been trying different versions of the following:
UPDATE table SET column b = md5((SELECT column a FROM table)) WHERE column a IS NOT NULL;
Any suggestions on how to perform this operation?
No need for a subquery here. As I understand, you want to store the checksum of
column_b. As one would expect, Postgres’
md5() function expects a single, scalar argument of string datatype, so:
UPDATE table SET column_b = md5(column_a) WHERE column_a IS NOT NULL;
Note that it would probably be simpler to use a computed column (available in Postgres 12) to store this derived information.