Append a string in a update query
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
Comments
Hi,
you can use the concat function:
select {fn CONCAT('HELLO',' world')}
Its not working in Update query .
Update tablename set column1 = {fn CONCAT('column1, ' world')} where ....
Is it something like this ?
<FONT COLOR="#0000ff">Update </FONT><FONT COLOR="#008000">tablename </FONT><FONT COLOR="#000080">set </FONT><FONT COLOR="#008000">column1 </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">column1 </FONT><FONT COLOR="#000000">|| </FONT><FONT COLOR="#008080">' world' </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#000000">...</FONT>
This is not working . When I running through win sql , It shows '[%' but not updating.
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) AffectedCan we see the error number and error text?.png)
Please see the win sql result but its not updating
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.
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>]
Great, that's something.
Can you show the exact query text and the class source code for the SQLUser.PA_Person table?
Update SQLUser.PA_Person set PAPER_REMARK = PAPER_REMARK ||'abc' where PAPER_PAPMI_DR->PAPMI_No='1111' and PAPER_ROWID='2222'
An UPDATE example:
UPDATE Sample_DB.Greetings SET Greeting = {fn CONCAT(Greeting,' World')}This is not working . When I running through win sql , It shows '[%' but not updating.
Must be a WinSQL issue. I tested this via the Management Console, IRIS SQL Shell, and DBeaver/jdbc; all worked as expected.
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>]
Hi , Can anyone please provide an update.
Thanks
Jude
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.
@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?