with clause in an update statement

[Former Member]

Does SQLAnywhere 11 or 12 support this syntax:

UPDATE gtt_sav_query_detail
      SET status_code_description =
         (WITH fp AS
               (SELECT s.translation_id        AS tr_id
                  FROM gtt_sav_query_detail r,
                       s1_settlement_status s
                 WHERE s.status_code = r.status_code
                   AND s.settlement_type = r.settlement_type
                   AND (s.print_check = r.gs_print_flag OR
                        s.print_check IS NULL
                       )
                   AND (s.check_void_flag = r.check_void_flag OR
                        s.check_void_flag IS NULL
                       )
                   AND (s.payment_method = r.payment_method OR
                        s.payment_method IS NULL
                       )
                   AND ((s.check_no = 'Y' AND
                         r.check_no IS NOT NULL
                        ) OR
                        (s.check_no = 'N' AND
                         r.check_no IS NULL
                        ) OR
                        (s.check_no IS NULL)
                       )
                   AND ((s.amount = 'Y' AND
                         COALESCE(r.amount,0) > 0
                        ) OR
                        (s.amount = 'N' AND
                         COALESCE(r.amount,0) = 0
                        ) OR
                        (s.amount IS NULL)
                       )
               )
          SELECT t.description
               FROM a1_translation t,
                    fp
              WHERE t.language = lv_language
             AND t.id = fp.tr_id
             AND ROWID() <= 1
         );

Specifically, the "with" clause?

[Former Member]

look here in the documentation

http://dcx.sybase.com/index.html#sa160/en/dbusage/ug-commontblexpr.html

futher down there is an example using with

and here it states when you can use common table expressions:

http://dcx.sybase.com/index.html#1100/en/dbusage_en11/commontblexpr-s-7010660.html

http://dcx.sybase.com/index.html#sa160/en/dbusage/commontblexpr-s-7010660.html

[Breck_Carter]

Well hidden! 🙂

Real people say "WITH clause" or "WITH view", and never think in terms of "common table expression" 🙂

[Former Member]

I assure you I am a real person, although I have much more experience with Oracle than SQL Anywhere....

[VolkerBarth]

Have you tried it already?

I have not but I think it should be is not allowed because

1. according to the UPDATE syntax, SET column = expresssion allows a subquery as expression,
2. a subquery as search condition is a SELECT statement which mustn't return more than one column and row,
3. and a SELECT statement itself may use a WITH clause to specify a common table expression (CTE).
   

EDIT: Correction based on M G's answer:

1. Unfortunately, there's a further condition for CTEs:
   Common table expressions are permitted within top-level SELECT statements, but not within subqueries.

So in this case, the subquery nature of the SELECT seems to prevent the usage of a CTE.
That should explain the error message you are getting.

[Former Member]

I have tried it but it gives this error:

Could not execute statement. Syntax error near 'WITH' on line 3 SQLCODE=-131, ODBC 3 State="42000" Line 1, column 4 It points to the "with" part of the SQL.

[Former Member]

The links you provided (in paragraph 1 and 3) does not work. You end up in the sybase documentation but the page only shows

"Page does not exist (404)" and that you should go back to the DocCommentXchange home page.

[VolkerBarth]

Thanks for the pointer - in both cases I happened to omit the final "l" from the URL - seems to by my typical cut & paste error:)

[VolkerBarth]

A very late addition I just came across today:

While you cannot (still with v 17) use common table expressions (CTEs) with UPDATE statements, there's a workaround in case the table to update has a primary key:

The INSERT...SELECT statement with the ON EXISTING UPDATE clause can be used to to UPDATEs as well, and here the SELECT statement can contain common table expressions, so basically one can use

INSERT MyTable (column...list)
ON EXISTING UPDATE [DEFAULTS ON/OFF]
WITH MyCTE as (...).... -- using one or more CTEs
SELECT...-- query using the CTEs to generate the new values for the table to be updated...

I have currently used that approach with a simple ETL situation: A CTE would list current and up-coming row values next to each other (i.e. the result set would show the current "row contents" and the "up-coming row contents after the import" row by row), and after checking the correctness of the "up-coming row contents", just adding the according INSERT clause and reducing the result set to the new contents, the UPDATE was easily done.