2010/11/8 Pavel Stehule <pavel.stehule@gmail.com>:
> 2010/11/8 Hitoshi Harada <umi.tanuki@gmail.com>:
>> Hmm, this looks very different from our writeable CTEs. And I can see
>> many issues like syntax ambiguity and execution order if we support
>> it. AFAIK the most significant reason why we are working on CTEs is
>> that CTEs are regarded as something like materialized table.
>>
>> So I think we are going on writeable "CTEs" unless there are no
>> objection rather than pursuing the standard.
>> Thanks for sharing anyway.
>>
>
> I found, so writeable CTE was implemented in Microsoft SQL server too.
> Can be our implementation compatible?
>
I don't believe MS SQL Server doesn't implement such our writeable
CTEs. If you mention about this link
(http://msdn.microsoft.com/en-us/library/ms175972.aspx), it says:
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::= expression_name [ (column_name [ ,...n ] ) ] AS (CTE_query_definition)
and
CTE_query_definition
Specifies a SELECT statement whose result set populates the common
table expression. <snip>
It says about top-level DML under CTE shown below which we already
have in our HEAD.
WITH t AS (SELECT * FROM src) INSERT INTO dest SELECT * FROM t;
as "A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE,
or DELETE statement that references some or all the CTE columns. A CTE
can also be specified in a CREATE VIEW statement as part of the
defining SELECT statement of the view."
They are different.
Regards,
--
Hitoshi Harada