473,581 Members | 2,708 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

"Merge Into ..." Trouble

I'm going crazy trying to write a working merge-statement. Why doesn't
the following work:

MERGE INTO utSystem.t_Filt er_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN NOT MATCHED AND FIl.MemberID IS NULL THEN
DELETE

WHEN NOT MATCHED AND FIl.MemberID IS NOT NULL THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

ELSE IGNORE
Error returned is: SQL0104N Auf "emberID IS NULL THEN" folgte das
unerwartete Token "DELETE". Zu den möglichen Token gehören:
"<merge_when_sp ec_body2>". SQLSTATE=42601. Sorry for German message.
It says that the token is not allowed in the context.

Also if I try replacing "AND Fil.MemberID IS NOT NULL" by an expression
using Res.MemberID it also says that its not allowed in the context.

Is the problem that if I filter by "NOT MATCHED" then there is no
cursor on a column to delte?

Regards
Janick

Nov 12 '05 #1
5 3317
Janick Bernet wrote:
I'm going crazy trying to write a working merge-statement. Why doesn't
the following work:

MERGE INTO utSystem.t_Filt er_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN NOT MATCHED AND FIl.MemberID IS NULL THEN
DELETE

WHEN NOT MATCHED AND FIl.MemberID IS NOT NULL THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

ELSE IGNORE
Error returned is: SQL0104N Auf "emberID IS NULL THEN" folgte das
unerwartete Token "DELETE". Zu den möglichen Token gehören:
"<merge_when_sp ec_body2>". SQLSTATE=42601. Sorry for German message.
It says that the token is not allowed in the context.


I think you have a problem with the DELETE. The documentation says:

"Rows in the target that match the source can be deleted or updated as
specified, and rows that do not exist in the target can be inserted."

You have the case "NOT MATCHED", so you can only use INSERT as the
<modification-operation>, I'd say. After all, what is DB2 supposed to
delete if there is nothing that matched?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #2
Knut Stolze wrote:
Janick Bernet wrote:

I'm going crazy trying to write a working merge-statement. Why doesn't
the following work:

MERGE INTO utSystem.t_Filt er_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN NOT MATCHED AND FIl.MemberID IS NULL THEN
DELETE

WHEN NOT MATCHED AND FIl.MemberID IS NOT NULL THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

ELSE IGNORE
Error returned is: SQL0104N Auf "emberID IS NULL THEN" folgte das
unerwartete Token "DELETE". Zu den möglichen Token gehören:
"<merge_when_ spec_body2>". SQLSTATE=42601. Sorry for German message.
It says that the token is not allowed in the context.

I think you have a problem with the DELETE. The documentation says:

"Rows in the target that match the source can be deleted or updated as
specified, and rows that do not exist in the target can be inserted."

You have the case "NOT MATCHED", so you can only use INSERT as the
<modification-operation>, I'd say. After all, what is DB2 supposed to
delete if there is nothing that matched?

Correct, try this:
MERGE INTO utSystem.t_Filt er_Results AS Res
USING (SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9
WHERE (("LOCATION#" = 'Zürich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID

WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3
Knut, Serge thanks for you answers. If Knut is right, merge will not be
able to do what I want.

The Idea is to only insert rows which are in the table "Fil", to delete
rows which are not in the table "Fil" and to leave rows which are in
both tables untouched. So Serges solution does work, but doesn't do
what I want to :) Can I do with merge what I intended or is it not
suited for this? Is there another solution, better than delete all old
rows and then insert the new ones?

Regards
Janick

Nov 12 '05 #4
In my understanding, you want to do........
From these two sample data

------------------------- Commands Entered -------------------------
SELECT * FROM utSystem.t_Filt er_Results
ORDER BY 1;
--------------------------------------------------------------------

CLUBID FILTERID MEMBERID
----------- ----------- -----------
100 50 1
200 50 2
300 50 3
400 40 3

4 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT * FROM club.vc_9;
--------------------------------------------------------------------

ID LOCATION# ENTERING_DATE LEAVING_DATE
----------- --------- ------------- ------------
1 Z?rich 2005-10-19 -
2 ZXrich 2005-10-19 -
3 Z?rich 2005-10-14 -
4 Z?rich 2005-10-29 2005-11-03

4 record(s) selected.

Get following result
------------------------- Commands Entered -------------------------
SELECT * FROM utSystem.t_Filt er_Results;
--------------------------------------------------------------------

CLUBID FILTERID MEMBERID
----------- ----------- -----------
300 50 3
9 50 4
100 50 1

3 record(s) selected.

If so, one idea is DELETE and INSERT separately:
DELETE FROM utSystem.t_Filt er_Results Res
WHERE NOT EXISTS
(SELECT *
FROM club.vc_9 Vc9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
AND Res.MemberID = Vc9.ID
AND Res.FilterID = 50
);

INSERT INTO utSystem.t_Filt er_Results
WITH Fil AS (
SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9 Vc9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS NULL))
)
SELECT Fil.ClubID, Fil.FilterID, Fil.MemberID
FROM Fil
WHERE NOT EXISTS
(SELECT *
FROM utSystem.t_Filt er_Results Res
WHERE Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID
);

My another idea using MERGE is something complicated and I suspect
performance. But, I'll show you it for your reference:
MERGE INTO utSystem.t_Filt er_Results AS Res
USING (SELECT 9 as clubid, COALESCE(R.Filt erID, 50) as filterid
, COALESCE(R.Memb erID, V.ID) as MemberID
, CASE
WHEN V.ID IS NULL THEN
2
WHEN R.MemberID IS NULL
AND R.FilterID IS NULL THEN
1
ELSE 0
END AS Matched
FROM (SELECT *
FROM club.vc_9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE
IS NULL))
) AS V
FULL OUTER JOIN
utSystem.t_Filt er_Results AS R
ON R.MemberID = V.ID
AND R.FilterID = 50
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID
AND 2 = Fil.Matched
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED AND 1 = Fil.Matched THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)
;

Nov 12 '05 #5
Tonkuma, thanks a lot for your detailed answer. I think I will go with
the first approach, which should be the faster and more readable one :)

Regards
Janick

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3902
by: Sean Dettrick | last post by:
Hi, I have several <vector>'s of the same length, with entries as follows: I= A= B= I want to use STL to make (I == 0) a mask to operate on the elements of A and B, i.e. I want to do this:
3
4584
by: anurag | last post by:
Hi, Is "MERGE INTO..." statement allowed to be executed in a dynamic SQL statement, inside an SQL Stored Procedure? I am using DB2 UDB 8.1.2 on Windows. If it is not allowed (as I suspect), how do I go about converting an Oracle Procedure that dynamically executes "MERGE INTO..."? ONE RESTRICTION: My converted code has to execute faster...
1
2433
by: mcmreddy | last post by:
Hello, Is there any advantage of using "MERGE INTO" SQL instead of using "EXISTS" AND "NOT EXISTS" in DB2?. Thanks, --Chandra
2
5129
by: Lee | last post by:
I have a Word template that is set to merge with our client database. After I run the merge, I merge to a new document, and then save the new document. When I reopen this saved document, it prompts Access to open and request information from the underlying query. Since I've already merged and saved the new document with the merged info, I...
0
1641
by: Sheila | last post by:
I have set up a Word mail merge document of the type Directory which allows me to display all of the records on a single page rather than putting each record on a separate page. It uses a temporary table in MS Access as its datasource (loaded via a Make Table query). If I create the temporary table and then open the mail merge document from...
3
1787
by: MirzaSila | last post by:
I am using mail merge option in msWord for creating labels, data source is msAccess. Problem is when I choose option "merge to printer" it only prints the curent page even though I completed the merge.I'am not sure is it printer proporties causing the problem or something else? Any reply would be appreciated.
2
3291
by: john sun | last post by:
Hi, Dear gurus, In the web deployment project, there is one option called "Output Assemblies page". The first choice is "Merge all outputs to a single assembly", According to MSDN, "Merges all the output assemblies from the compiler into a single assembly. This setting is equivalent to the -o assemblyname option of
0
1581
by: Quisbamsis | last post by:
Access Database Merge to Publisher or Word. Everytime I run a "Mail and Catalogue Merge Wizard" in Publisher or Word and I get to the "Mail Merge Recipients" dialogue box, my edit button is deselected, and I can't make any edits to the selected Access database. I have to close out of Publisher, and launch my Access database with the...
0
3972
by: jeoffh | last post by:
Background: I am trying to "merge" some attributes into an existing XML column in my MS SQL 2005 database. The general idea is that I have an XML column in a table and I would like to update/delete some values while leaving the other values alone. I am designing this database/table/column so maybe I could use attributes or elements/nodes, the...
0
7854
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7783
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8296
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7878
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8157
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6533
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5665
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
1
2295
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1394
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.