×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
Hi all,
I'm not sure if this is allowed so I'm checking with the group.
I'm trying to use a common table expression (CTE) with a MERGE statement.
Glenn,
It looks like the table reference in the MERGE statement does not support common table expressions, although it appears from the documentation it should.
I tested using the following script:
Create Table
QTEMP.NEWTABLE As (
Select
Cast(0 As Integer) As ID,
Cast('' As Char(10)) As NAME
From
(Values 1) As A
) With No Data;
-- This doesn't work
-- Fails with SQL State 42601
Merge Into
QTEMP.NEWTABLE As OLD
Using
(With MYCTE As (
Select
Cast(1 As Integer) As ID,
Cast('JIM' As Char(10)) As NAME
From
(Values 1) As A)
Select
*
From
MYCTE) As NEW
On
(OLD.ID = NEW.ID)
When Not matched Then
Insert Values(NEW.ID, NEW.NAME)
When Matched Then
Update Set OLD.NAME = NEW.NAME;
-- This does work
Merge Into
QTEMP.NEWTABLE As OLD
Using
(
Select
Cast(1 As Integer) As ID,
Cast('JIM' As Char(10)) As NAME
From
(Values 1) As A) As NEW
On
(OLD.ID = NEW.ID)
When Not matched Then
Insert Values(NEW.ID, NEW.NAME)
When Matched Then
Update Set OLD.NAME = NEW.NAME;
Jim
midrange.com
