Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Take this example

create table Test1 (colA integer);
create table Test2 (colB integer);

insert into Test2 (colB)
select new1.colA
  from (insert into Test1 (colA) values (1)) referencing (final as new1)

After this tables Test1 and Test2 both contain 1 record, which is what I expected. Now try this:

create table Test3 (colA integer);
create table Test4 (colB integer);
create table Test5 (colC integer);

insert into Test5 (colC)
select new4.colB
  from (insert into Test4 (colB)
        select new3.colA
          from (insert into Test3 (colA) values (2)
               ) referencing (final as new3)
       ) referencing (final as new4)

You don't get any errors, but the result is not what I expected. Only in table Test3 a record is inserted. Tables Test4 and Test5 remain empty.

From the help: "The dml-derived-table statement can only reference one updatable table; updates over multiple tables return an error. Also, selecting from dml-statement is not allowed if the DML statement appears inside a correlated subquery or common table expression because the semantics of these constructs can be unclear."

I'm not quite sure if this last part in the help is trying to explain that what I'm trying to do here doesn't work.
So basically my question is: is this a known limitation or a bug?
And if it's a known limitation: an exception would be appreciated in such a situation so it's clear immediately that this is not supported.

asked 02 Feb, 06:03

Christian%20Hamers's gravatar image

Christian Ha...
697151833
accept rate: 42%

FWIW, the help does show an example with multiple DML-derived queries within one statement - but those are not nested but joined together:

SELECT Over a DML Statement

(07 Feb, 03:38) Volker Barth
Be the first one to answer this question!
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:

×248
×7

question asked: 02 Feb, 06:03

question was seen: 313 times

last updated: 09 Feb, 03:37