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?

Add Comment
1 Answer(s)

No need for a subquery here. As I understand, you want to store the checksum of column_a in 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.

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.