concat multiple strings of a column in different update statements-CodePudding

I need to update the column Remark that depends on the column in different update statements if it has an update and my code below doesn't work

It also needs to be separated by a comma (,)

UPDATE %Table(staging_tbl) S
SET S.REMARK= %Concat(S.REMARK || ',email update'), A.EMAIL = (SELECT A.EMAIL .....)


UPDATE %Table(staging_tbl) S
SET S.REMARK= %Concat(S.REMARK || ',age update'), A.AGE = (SELECT A.AGE.....)

UPDATE %Table(staging_tbl) S
SET S.REMARK= %Concat(S.REMARK || ',name update'), A.NAME = (SELECT A.NAME.....)

What can I add if the string to be added depends on if there's an update in that column?

CodePudding user response：

In Oracle, use a single statement and concatenate the strings using a CASE expression to determine whether there were any changes:

UPDATE staging_tbl
SET age    = :age,
    email  = :email,
    name   = :name,
    remark = remark
             || CASE WHEN :age   <> age   THEN ',age updated'   END
             || CASE WHEN :email <> email THEN ',email updated' END
             || CASE WHEN :name  <> name  THEN ',name updated'  END
WHERE id = :id

db<>fiddle here

CodePudding user response：

I believe what you want is:

S.REMARK= S.REMARK %Concat ',email update'

%Concat is not function-like, such as %Table.