concat multiple strings of a column in different update statements

Question

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?

So you want the concatenation to occur only when an update occurs? If yes, maybe use a trigger. Otherwise why not update table_name set remark = concat(remark, 'the text part here') — Rich Bianco, Jul 28 '22 at 00:06
@Bohemian this code is supposed to run in an app engine and will print in a csv file, and it depends on the user what they update online so there's a case where the email and age have updates and the name has none — Cas Cordero, Jul 28 '22 at 00:19
@RichBianco yes but what trigger is that? the remark column could have 2 or more strings so I think i should use the || operator but I still get an error message — Cas Cordero, Jul 28 '22 at 00:20
In my opinion you should use concat or the || not both, but that's what I see. Sorry if I couldn't help — Rich Bianco, Jul 28 '22 at 00:24
@RichBianco no thanks for your opinion appreciate it. I will try it — Cas Cordero, Jul 28 '22 at 01:32
That's not Oracle SQL; at least, I've never seen it looking like that. If you use PeopleSoft, remove the "Oracle" tag as it shouldn't be used for products owned by Oracle Corporation. As of the question itself: perhaps you should post an example which shows what you have, and what you'd want to get as a result. — Littlefoot, Jul 28 '22 at 06:02
Selecting the expression when you need it, perhaps via a view, would work instead of storing a column. Why not use that instead? — Bohemian, Jul 28 '22 at 15:12

score 2 · Accepted Answer · answered Jul 28 '22 at 07:43

2

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

answered Jul 28 '22 at 07:43

MT0

143,790
11
59
117

thank you for this! i get the logic but I don't know how to declare an unknown value I have two tables the staging table and the main table and the main table is the one i'm declaring to and the update will take place in staging is that correct? – Cas Cordero Aug 09 '22 at 08:51

score 2 · Answer 2 · answered Jul 29 '22 at 17:41

2

I believe what you want is:

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

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

answered Jul 29 '22 at 17:41

jim.marion

149
2

Yes but I also need a condition to update the remark column based on the updates that take place in other column – Cas Cordero Aug 09 '22 at 08:52

concat multiple strings of a column in different update statements

2 Answers2