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?

asked 18 Feb '14, 11:50

Murray%20Sobol's gravatar image

Murray Sobol
(suspended)
accept rate: 0%

edited 18 Feb '14, 12:42

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297


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.

permanent link

answered 18 Feb '14, 15:37

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

edited 19 Feb '14, 06:47

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.

(18 Feb '14, 16:06) Murray Sobol

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.

(19 Feb '14, 02:28) M G
Replies hidden

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:)

(19 Feb '14, 03:37) Volker Barth
permanent link

answered 19 Feb '14, 02:53

M%20G's gravatar image

M G
629253044
accept rate: 42%

edited 19 Feb '14, 03:01

Well hidden! :)

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

(19 Feb '14, 06:40) Breck Carter

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

(19 Feb '14, 09:15) M G

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.

permanent link

answered 24 Apr '23, 13:02

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×38
×36

question asked: 18 Feb '14, 11:50

question was seen: 14,645 times

last updated: 24 Apr '23, 13:02