Search code examples
sqlpostgresqlsql-updatesubquery

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?


Solution

  • 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.