CODEWITHSUNDEEP
sqlpostgresql
wzzrd
wzzrd

Reputation: 620

SQL update statement to change the value of a field and not replace it

I'm in the process of migrating some databases. I have this table that has a couple of hundred rows, and has a filename column. To each record in this table, the filename column needs to be altered and part of a path needs to be prepended to the value that is in that field.

The table is like:

| 1 | filename1 |
| 2 | filename2 |

and needs to become:

| 1 | path/filename1 |
| 2 | path/filename2 |

I am not an SQL guru, but I know the basics. This eludes me though. Is there a way to do something like:

update table 
   set filename = 'path/' + filename 
 where id = 1;

Upvotes: 2

Views: 2025

Answers (4)

Alison R.
Alison R.

Reputation: 4294

You pretty much have it right there. You don't need to specify a where clause if you want to do it for all the rows, so then it would just be:

update table set filename = 'path/' || filename;

(|| is the concatenation operator in PostgreSQL)

Upvotes: 9

HLGEM
HLGEM

Reputation: 96542

They have told you how to write teh concatenation, I suggest you run this selct first to see waht your results will be:

select filename, 'path/'|| filename  from table
where id = 1;

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425251

UPDATE  table
SET     filename = 'path/' || filename
WHERE   id = 1

Upvotes: -1

jeanreis
jeanreis

Reputation: 908

I think this should work:

UPDATE table SET filename = CONCAT("path/", filename);

Upvotes: 0

Related Questions