Use a function to update a column

[Former Member]

I want to reformat a string in one column of a table, and put that new value in another column of the same table. I created a UDF, and attempt the UPDATE as so:

UPDATE MYTABLE SET REFORMATTED_STRING = FCN_REFORMAT_STRING(ORIGINAL_STRING);

But I get this error: SAP DBTech JDBC: [7]: feature not supported: cannot support non-constant types

Is this possible to do?

Thanks

[pfefferf · Accepted solution]

Hello Jay,

unfortunately you can use a scalar UDF only in the SELECT or WHERE clause of a SELECT statement, but not directly in an UPDATE statement.

You can transform your UPDATE statement to use a subselect to be able to use your UDF. Something like following:

UPDATE MYTABLE as "T1"

  SET "REFORMATTED_STRING" = ( SELECT FCN_REFORMAT_STRING("ORIGINAL_STRING") as "REFORMATTED_STRING" FROM MYTABLE as "T2" WHERE "T1".<key> = "T2".<key>");

Of course you can do your reformat also within a procedure or depending on the complexity of your reformat directly with the UPDATE statement using expression.

Best regards,

Florian