AXL SQL Update syntax for an update with a join clause

Version 1
    This document was generated from CDN thread

    Created by: chris crowe on 05-03-2010 12:18:04 AM
    I am trying to do a simple update  but with a set of complex joins and I do not know the syntax of the AXL to perform this.

    My AXL SQL Code is

    update numplan
    set alertingname = CONCAT(CONCAT(EndUser.firstname,  ' '), EndUser.lastname)
    from Device
    INNER JOIN EndUser ON Device.fkenduser = EndUser.pkid
    INNER JOIN DeviceNumPlanMap ON Device.pkid = DeviceNumPlanMap.fkDevice
    INNER JOIN NumPlan ON DeviceNumPlanMap.fkNumPlan = NumPlan.pkid
    INNER JOIN TypeModel tm on tm.enum =  Device .tkmodel and Device.tkclass=1
    where (upper(EndUser.userid) = 'CCROWE') and (numplan.alertingname = '') and tm.enum IN (446, 412, 30008, 20000, 369, 6, 307, 30007, 30002, 365, 484, 348, 9, 30019, 431, 8, 115, 309, 434, 435, 7, 30018, 308, 404, 436, 30006, 119, 437, 302, 30016)


    I am getting the standard 201 - A syntax error has occurred.

    I assume someone just needs to point me in the right idea for the update syntax for an update statement with a join in it.

    Cheers

    Chris

    Subject: RE: AXL SQL Update syntax for an update with a join clause
    Replied by: David Staudt on 05-03-2010 02:56:28 AM
    Three possible hints:
     
    - The underlying database is Informix IDS.  Their online documentation should provide syntax specifics: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp
    - Check the AXL service logs (via the RTMT), you can increase the logging verbosity and possibly get more details on the error.
    - You can execute SQL via the UCM command line: SSH to the UCM IP address, login and execute:
     
       run sql [my SQL statement here]
     
    You may get more detailed hints/error messages this way.

    Subject: RE: AXL SQL Update syntax for an update with a join clause
    Replied by: chris crowe on 05-03-2010 10:00:39 PM
    Three possible hints:
     
    - The underlying database is Informix IDS.  Their online documentation should provide syntax specifics: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp
    - Check the AXL service logs (via the RTMT), you can increase the logging verbosity and possibly get more details on the error.
    - You can execute SQL via the UCM command line: SSH to the UCM IP address, login and execute:

     
    Thanks David
     
    Option #1 helped a bit but not much (more below)
    Option #2 Not sure how to do this
    Option #3 Tried this and got "Syntax error"
     
    OK - So I found a link to the docs on update
    http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqlt.doc/sqltmst150.htm?resultof=%22%75%70%64%61%74%65%22%20%22%75%70%64%61%74%22%20%22%6a%6f%69%6e%22%20
     
    Basically the syntax is this
     
    UPDATE t SET a = t2.a FROM t, t2 WHERE t.b = t2.b
     
    but it did not  help
     
    I reduced my complex SQL to this
     
    update device set alertingname = 'CHRIS' from device, enduser where enduser.userid = 'CCROWE' and device.fkenduser = enduser.pkid

     
    Still syntax error
     
    No better off - maybe it can not do a join in an update? you can not get much simpiler than that.
     
    The only other exampel I have found here that uses an update with a join the user also got Syntax error.
     
    You tried to help him as well David - see http://developer.cisco.com/web/axl/forums/-/message_boards/message/1667790?_19_redirect=%2Fweb%2Faxl%2Fforums%2F-%2Fmessage_boards%2Fsearch%3F_19_redirect%3D%252Fweb%252Faxl%252Fforums%252F-%252Fmessage_boards%252Fcategory%252F1052601%26_19_breadcrumbsCategoryId%3D1052601%26_19_searchCategoryId%3D1052601%26_19_tabs1TabsScroll%3D%26_19_keywords%3Dsyntax%2Berror
     
    chris

    Subject: RE: AXL SQL Update syntax for an update with a join clause
    Replied by: chris crowe on 05-03-2010 10:10:48 PM
    Another user with the exact same issue
     
    https://supportforums.cisco.com/thread/292647.pdf;jsessionid=F8D864ECA2E5ADEB84E3F675F00B27F7.node0
     
    chris

    Subject: RE: AXL SQL Update syntax for an update with a join clause
    Replied by: David Staudt on 06-03-2010 01:23:07 AM
    The doc link you gave is interesting, but it does indicate this is a feature of the IBM 'Extended Parallel Server' a high-end datawharehousing product, I think...UCM uses the Dynamic Server product - more geared toward embedded, high-performance.  The effects of this query could be pretty subtle and difficult to implement robustly.
     
    I restated the SQL in a somewhat different way (note device.alerting name doesn't exist in UCM7...what version are you testing?):
     
    update device set description='myPhone' where device.pkid in
      (select device.pkid from device, enduser where device.fkenduser=enduser.pkid and enduser.userid='dstaudt')
     
    UCM returned this, more infomative, error:
     
    Cannot modify table or view used in subquery
     
    This kind of hints at the possible difficulties in implementing the syntax the doc link describes.
     
    Based on this, I fear you would need to perform a two-step process:
     
    select device.pkid from device, enduser where
    device.fkenduser=enduser.pkid and enduser.userid='dstaudt'

     
    Take the returned id's and concatenate into a string for the update:
     
    update device set description='myPhone' where device.pkid in ('{device-id1-example}','{device-id2-example}',...)
     
    Without spending more time testing, my guess would be you can go ahead and join your other tables to one piece or the other to get the complete effect.
     
     

    Subject: RE: New Message from David Staudt in Administration XML (AXL) - Administrat
    Replied by: chris crowe on 06-03-2010 05:22:58 AM
    Thanks for the follow-up David.

    Good spotting on my error on the alertingname ¿ it is actually in the NumPlan table (my bad). I am using v6.x

    I do not however think that is the cause of the error.

    You are probably correct by the looks of things in regards to the SQL implementation being at fault and not supporting joins in the update statements.

    I tried or SQL Snippet and got the exact some error message.

    I can extract it out and run it manually as you say but I believe that there is a limit of the number of update queries you can run in a specific period of time.

    I have not done updates before only selects so it is all new to me.

    Thank you very much for taking the time to reply to me, it is very much appreciated.

    Cheers

    Chris


    From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
    Sent: Saturday, 6 March 2010 2:23 p.m.
    To: cdicuser@developer.cisco.com
    Subject: New Message from David Staudt in Administration XML (AXL) - Administration XML Questions: RE: AXL SQL Update syntax for an update with a join clause

    David Staudt has created a new message in the forum "Administration XML Questions":
    --------------------------------------------------------------
    The doc link you gave is interesting, but it does indicate this is a feature of the IBM 'Extended Parallel Server' a high-end datawharehousing product, I think...UCM uses the Dynamic Server product - more geared toward embedded, high-performance.  The effects of this query could be pretty subtle and difficult to implement robustly.

    I restated the SQL in a somewhat different way (note device.alerting name doesn't exist in UCM7...what version are you testing?):

    update device set description='myPhone' where device.pkid in
      (select device.pkid from device, enduser where device.fkenduser=enduser.pkid and enduser.userid='dstaudt')

    UCM returned this, more infomative, error:

    Cannot modify table or view used in subquery

    This kind of hints at the possible difficulties in implementing the syntax the doc link describes.

    Based on this, I fear you would need to perform a two-step process:

    select device.pkid from device, enduser where
    device.fkenduser=enduser.pkid and enduser.userid='dstaudt'

    Take the returned id's and concatenate into a string for the update:

    update device set description='myPhone' where device.pkid in ('{device-id1-example}','{device-id2-example}',...)

    Without spending more time testing, my guess would be you can go ahead and join your other tables to one piece or the other to get the complete effect.


    --
    To respond to this post, please click the following link:
    <http://developer.cisco.com/web/axl/forums/-/message_boards/message/2019007>
    or simply reply to this email.