Concatenate string in ALTER statement in REDSHIFT (syntax error)

Question

I want alter an external table in redshift. If execute this query:

alter table "name_table" 
set location 'a string' || 
TO_CHAR((getdate()-1)::date, 'YYYYMMDD') || '/';

give me a syntax error:

SQL Error [500310] [42601]: Amazon Invalid operation: syntax error at or near "||" Position: 135;

Maybe I must use dynamic SQL but I'm not sure how do that. Can someone help me?

EDIT: I try to use a procedure for alter an external table, this doesn't give me a syntax error but return an error like this:

You can't alter an external table with function or procedure.

Answer 1

Do you want update?

update "name_table" 
    set location = 'a string' || TO_CHAR((getdate()-1)::date, 'YYYYMMDD') || '/';

Answer 2

I don't think location can be dynamic. Though you issue this statement in SQL, you can't use standard operators to build the S3 path. It's the same limitation as in good old COPY TO statement (target file can't be dynamic).