Append a string in a update query

Question

Question

Jude Mukkadayil · May 13

#Databases #SQL #InterSystems IRIS #InterSystems IRIS for Health

Hi,

Can anyone please tell me how to append a string using a update query.

For eg : "Hello" is the string . I need to append the string world and it needs to be "Hello world".

Thanks

Jude

Product version: IRIS 2023.3

Discussion (17)3

Log in or sign up to continue

score 0 · Answer 1 · 2025-05-14T01:28:16-04:00

Timo Lindenschmid · May 14

Hi,
you can use the concat function:
select {fn CONCAT('HELLO',' world')}

0 0

score 0 · Answer 2 · 2025-05-15T03:03:14-04:00

Its not working in Update query .

Update tablename set column1 = {fn CONCAT('column1, ' world')} where ....

Is it something like this ?

score 0 · Answer 3 · 2025-05-15T03:17:17-04:00

Vitaliy Serdtsev · May 15

Update tablename set column1 = column1 || ' world' where ...

0 0

score 0 · Answer 4 · 2025-05-21T00:22:57-04:00

Jude Mukkadayil · May 21

This is not working . When I running through win sql , It shows '[%' but not updating.

0 0

score 0 · Answer 5 · 2025-05-21T01:22:37-04:00

Class dc.a Extends %Persistent
{

Property s As %String;

ClassMethod Test()
{
  &sql(truncate table dc.a)

  &sql(insert into dc.a(s) values('Hello'))
  d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.a").%Display()

  w !
  &sql(update dc.a set s=s||' world' where %ID=1)
  d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.a").%Display()
}

}

USER>d ##class(dc.a).Test()
ID      s
1       Hello
 
1 Rows(s) Affected
ID      s
1       Hello world
 
1 Rows(s) Affected

Can we see the error number and error text?

score 0 · Answer 6 · 2025-05-21T02:27:42-04:00

Jude Mukkadayil · May 21

Please see the win sql result but its not updating

0 0

score 0 · Answer 7 · 2025-05-21T02:34:07-04:00

Try to do my example and report the results.
Also, try to run your query not in win sql, but in the InterSystems Management Portal.

score 0 · Answer 8 · 2025-05-21T02:44:38-04:00

I tried using management portal and I got this error below.

[SQLCODE: <-415>:<Fatal error occurred within the SQL filer>]

[%msg: <Error occurring during UPDATE in table 'SQLUser.PA_Person': $ZE=<LIST>%SQLUpdate+40^User.PAPerson.1>]

score 0 · Answer 9 · 2025-05-21T02:58:21-04:00

Great, that's something.

Can you show the exact query text and the class source code for the SQLUser.PA_Person table?

score 0 · Answer 10 · 2025-05-21T03:03:49-04:00

Update SQLUser.PA_Person set PAPER_REMARK = PAPER_REMARK ||'abc' where PAPER_PAPMI_DR->PAPMI_No='1111' and PAPER_ROWID='2222'

score 0 · Answer 11 · 2025-05-14T07:39:24-04:00

Jeffrey Drumm · May 14

An UPDATE example:

UPDATE Sample_DB.Greetings SET Greeting = {fn CONCAT(Greeting,' World')}

0 0

score 0 · Answer 12 · 2025-05-21T00:22:37-04:00

Jude Mukkadayil · May 21

This is not working . When I running through win sql , It shows '[%' but not updating.

0 0

score 0 · Answer 13 · 2025-05-21T09:48:41-04:00

Must be a WinSQL issue. I tested this via the Management Console, IRIS SQL Shell, and DBeaver/jdbc; all worked as expected.

score 0 · Answer 14 · 2025-05-23T03:07:27-04:00

Same error when I tried through management portal.

[SQLCODE: <-415>:<Fatal error occurred within the SQL filer>]

[%msg: <Error occurring during UPDATE in table 'SQLUser.PA_Person': $ZE=<LIST>%SQLUpdate+40^User.PAPerson.1>]

score 0 · Answer 15 · 2025-05-29T19:50:08-04:00

Jude Mukkadayil · May 29

Hi , Can anyone please provide an update.

Thanks

Jude

0 0

score 0 · Answer 16 · 2025-06-11T14:44:50-04:00

Hi Jude
Seems like, to append a string using an update query, use the concatenation operator "||". For example, if you have a column that holds "Hello" and you want to end up with "Hello World", you can write:

UPDATE your_table
SET your_column = your_column || ' world'
WHERE your_condition;

If you're working with pointer fields in InterSystems IRIS (like accessing a property, for example, "PAPER_PAPMI_DR.PAPMI_No") rather than using "->", be sure to use dot notation. So your updated query might look like:

UPDATE SQLUser.PA_Person
SET PAPER_REMARK = PAPER_REMARK || 'abc'
WHERE PAPER_PAPMI_DR.PAPMI_No = '1111'
  AND PAPER_ROWID = '2222'

This method efficiently appends the string to your existing data.

score 0 · Answer 17 · 2025-06-11T15:04:37-04:00

@Jude Mukkadayil in one of your posts here, you mentioned this error:

[SQLCODE: <-415>:<Fatal error occurred within the SQL filer>]

[%msg: <Error occurring during UPDATE in table 'SQLUser.PA_Person': $ZE=<LIST>%SQLUpdate+40^User.PAPerson.1>]

According to this documentation, that could be a runtime error in some trigger code. In your User.PA_Person class's ObjectScript, you probably have a trigger defined that is causing issues for you. If so, can you post the code for the trigger(s) in that class?