CODEWITHSUNDEEP
postgresqlsql-updatesubqueryconcatenation
MStikh
MStikh

Reputation: 404

Concatenate two columns and replace one of them with a result in postgresql

I have a table where in column location there is the folder of the file, e.g. '/home/ubuntu/test' and in the name column, there is the filename e.g. 'test1.png'.

I would like to replace the location column with the full path, e.g. '/home/ubuntu/test/test.png'.

I tried this:

UPDATE experiment_11.microscope_image_files
SET location=(SELECT concat_ws('/', location::text, name::text) 
FROM experiment_11.microscope_image_files);

but I get the following error:

ERROR: more than one row returned by a subquery used as an expression

Upvotes: 2

Views: 933

Answers (1)

Bohemian
Bohemian

Reputation: 425208

You don't want a subquery. Instead, use just:

UPDATE experiment_11.microscope_image_files SET
location = location || '/' || name

The error was caused by the subquery returning more than 1 row ( actually it was returning all the rows), but when used as an expression (as you did) it must return either 0 or 1 rows.

Upvotes: 2

Related Questions