on 2014 Feb 18 11:50 AM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Have you tried it already?
I have not but I think it should be is not allowed because
EDIT: Correction based on M G's answer:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
70 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.