November 21, 2011 at 8:14 am
Need help merging fields, i am getting this error from this code.
"Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows."
Any Ideas??
INSERT INTO XDW.dbo.tbl_Xclaimflag
([Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) SELECT
[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
FROM ( MERGE INTO XDW.dbo.tbl_Xclaimflag AS DST USING X.dbo.tbl_Xclaimflag AS SRC ON (SRC.Xclaimflag_id = DST.Xclaimflag_id) WHEN NOT MATCHED THEN INSERT
([Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) VALUES
([Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
) WHEN MATCHED AND DST.[Current] = 1 AND (ISNULL(SRC.[Xclaim_id],0) <> ISNULL(DST.[Xclaim_id],0)
OR ISNULL(DST.[Xclaimflagtype_id],0) <> ISNULL(SRC.[Xclaimflagtype_id],0)
OR ISNULL(DST.[value],'') <> ISNULL(SRC.[value],'')
) THEN UPDATE SET DST.[Current] = 0, DST.[EndEffectiveDate] = getDate() OUTPUT SRC.[Xclaimflag_id]
,SRC.[Xclaim_id]
,SRC.[Xclaimflagtype_id]
,SRC.[value]
,SRC.[warehoused]
, $Action AS MergeAction ) AS MRG WHERE MRG.MergeAction = 'UPDATE'
THANKS !!!!!
November 21, 2011 at 8:46 am
One more bit of information, there are duplicate flagID's, with different warehoused dates. Where would I insert the where or having statement to pull the earliest warehoused date?
I think this may fix the problem.
November 21, 2011 at 8:47 am
Could you provide some sample data?
It sounds like you're either returning duplicate records, or your logic doesn't provide mutually exclusive sets.
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
November 21, 2011 at 8:50 am
There is at least one situation where the criteria defined in your MERGE and MATCHED statements result in more than one row from SRC being used to update a single row in the DST. Use something like this to track down the offending rows, and then hopefully you'll be able to figure out what needs to be changed in order to get rid of the duplicates:
SELECT SRC.Xclaimflag_id, SRC.Xclaim_id, SRC.Xclaimflagtype_id, SRC.value
FROM X.dbo.tbl_Xclaimflag AS SRC INNER JOIN
XDW.dbo.tbl_Xclaimflag AS DST ON SRC.Xclaimflag_id = DST.Xclaimflag_id
WHERE DST.[Current] = 1
AND (
ISNULL(SRC.[Xclaim_id],0) <> ISNULL(DST.[Xclaim_id],0) OR
ISNULL(DST.[Xclaimflagtype_id],0) <> ISNULL(SRC.[Xclaimflagtype_id],0) OR
ISNULL(DST.[value],'') <> ISNULL(SRC.[value],'')
)
GROUP BY SRC.Xclaimflag_id, SRC.Xclaim_id, SRC.Xclaimflagtype_id, SRC.value
HAVING COUNT(*) > 1
If that doesn't work, please post table creation statements for SRC and DST along with some sample data for each, and I'll take another stab at it.
November 21, 2011 at 9:02 am
dan.k.williams (11/21/2011)
One more bit of information, there are duplicate flagID's, with different warehoused dates. Where would I insert the where or having statement to pull the earliest warehoused date?I think this may fix the problem.
Oops, I was writing my response when you posted that. Are you wanting to aggregate the source rows and get the MIN() of the warehoused date for both the INSERT and the UPDATE operations, or just for the UPDATE? If for both, you should be able to use a derived table in your USING clause that groups by Xclaimflagtype_id and returns the MIN() warehoused date.
November 21, 2011 at 9:05 am
Something like:
USING (
SELECT Xclaimflag_id, MIN(warehoused) AS warehoused
FROM X.dbo.tbl_Xclaimflag
GROUP BY Xclaimflag_id
)AS SRC ON (SRC.Xclaimflag_id = DST.Xclaimflag_id AND SRC.warehoused = DST.warehoused)
(Assuming that "warehoused" is the name of the field containing the warehoused date)
November 21, 2011 at 9:15 am
SRC create
USE [X]
GO
/****** Object: Table [dbo].[tbl_Xclaimflag] Script Date: 11/21/2011 10:06:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Xclaimflag](
[Xclaimflag_id] [int] NULL,
[Xclaim_id] [int] NULL,
[Xclaimflagtype_id] [int] NULL,
[value] [nvarchar](32) NULL,
[warehoused] [datetime] NULL,
[DW_ID] [int] IDENTITY(1,1) NOT NULL,
[BeginEffectiveDate] [datetime] NULL,
[EndEffectiveDate] [datetime] NULL,
[current] [bit] NULL
) ON [PRIMARY]
GO
SRC sample data
Xclaimflag_id Xclaim_id Xclaimflagtype_id valuewarehoused DW_ID BeginEffectiveDate EndEffectiveDate current
1 265 1 1 2011-01-14 05:00:00.000273592011-01-14 07:00:00.0002011-11-03 14:45:50.000 0
1 265 1 1 2011-01-14 07:00:00.000274202011-01-14 07:00:00.0002011-11-03 14:45:50.000 0
2 37 1 1 2011-01-14 05:00:00.000273602011-01-14 05:00:00.0002011-01-14 07:00:00.000 0
2 37 1 1 2011-01-14 07:00:00.000274212011-01-14 07:00:00.0002011-11-03 14:45:50.000 0
2 37 1 1 2011-11-15 15:15:04.000345446NULL NULL NULL
2 37 1 1 2011-11-16 09:15:04.000349877NULL NULL NULL
4 487 1 1 2011-01-14 05:00:00.000273622011-01-14 05:00:00.0002011-01-14 07:00:00.000 0
4 487 1 1 2011-01-14 07:00:00.000274232011-01-14 07:00:00.0002011-11-03 14:45:50.000 0
8 999 1 1 2011-11-21 07:00:00.000 99999 2011-11-21 07:00:00.000 2011-11-21 07:00:00.000 1
DST Create
USE [XDW]
GO
/****** Object: Table [dbo].[tbl_Xclaimflag] Script Date: 11/21/2011 09:52:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Xclaimflag](
[Xclaimflag_id] [int] NULL,
[Xclaim_id] [int] NULL,
[Xclaimflagtype_id] [int] NULL,
[value] [nvarchar](32) NULL,
[warehoused] [datetime] NULL,
[DW_ID] [int] IDENTITY(1,1) NOT NULL,
[BeginEffectiveDate] [datetime] NULL,
[EndEffectiveDate] [datetime] NULL,
[current] [bit] NULL
) ON [PRIMARY]
GO
DST Sample data
Xclaimflag_idXclaim_idXclaimflagtype_idvaluewarehoused DW_IDBeginEffectiveDate EndEffectiveDate current
1 265 1 1 2011-01-14 05:00:00.000273592011-01-14 05:00:00.0002011-01-14 07:00:00.000 0
1 265 1 1 2011-01-14 07:00:00.000274202011-01-14 07:00:00.000NULL 1
2 37 1 1 2011-01-14 05:00:00.000273602011-01-14 05:00:00.0002011-01-14 07:00:00.000 0
2 37 1 1 2011-01-14 07:00:00.000274212011-01-14 07:00:00.000NULL 1
4 487 1 1 2011-01-14 05:00:00.000273622011-01-14 05:00:00.0002011-01-14 07:00:00.000 0
4 487 1 1 2011-01-14 07:00:00.000274232011-01-14 07:00:00.000NULL 1
November 21, 2011 at 9:25 am
INSERT INTO XDW.dbo.tbl_Xclaimflag
([Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) SELECT
[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
FROM ( MERGE INTO XDW.dbo.tbl_Xclaimflag AS DST USING (
SELECT Xclaimflag_id, MIN(warehoused) AS warehoused,Xclaim_id
,Xclaimflagtype_id
,value
FROM X.dbo.tbl_Xclaimflag
GROUP BY Xclaimflag_id, Xclaim_id, Xclaimflagtype_id, value
)AS SRC ON (SRC.Xclaimflag_id = DST.Xclaimflag_id AND SRC.warehoused = DST.warehoused)
WHEN NOT MATCHED THEN INSERT
([Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) VALUES
([Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
) WHEN MATCHED AND DST.[Current] = 1 AND (ISNULL(SRC.[Xclaim_id],0) <> ISNULL(DST.[Xclaim_id],0)
OR ISNULL(DST.[Xclaimflagtype_id],0) <> ISNULL(SRC.[Xclaimflagtype_id],0)
OR ISNULL(DST.[value],'') <> ISNULL(SRC.[value],'')
) THEN UPDATE SET DST.[Current] = 0, DST.[EndEffectiveDate] = getDate() OUTPUT SRC.[Xclaimflag_id]
,SRC.[Xclaim_id]
,SRC.[Xclaimflagtype_id]
,SRC.[value]
,SRC.[warehoused]
, $Action AS MergeAction ) AS MRG WHERE MRG.MergeAction = 'UPDATE'
Yes I think that will work, I just had to insert the rest of the columns into your new subselect and group by statement for SRC, so that they would be recognized in the update set.
Now to test it with some new data and see if it adds only new lines and not duplicates....
November 21, 2011 at 9:31 am
dan.k.williams (11/21/2011)
Yes I think that will work, I just had to insert the rest of the columns into your new subselect and group by statement for SRC, so that they would be recognized in the update set.Now to test it with some new data and see if it adds only new lines and not duplicates....
Great! Let me know if you still have issues with it, and I'll take a deeper look at it using the DDL and sample data you posted.
November 21, 2011 at 10:35 am
Eureka! It works!
I inserted several bunk lines of data with all possible permutations of differences into SRC. It wrote any row with differences and excluded any row with duplicates into DST.
It also grabbed the row with the earliest warehoused date when duplicates were found. Now I just have to do this for ~200 other tables :pinch:
Thanks JonFox!
November 21, 2011 at 10:53 am
You're welcome! Glad it worked for you!
November 21, 2011 at 12:17 pm
Hey another error...
In another table which I have to apply this logic on...
there exists an "ntext" field, it is throwing this error
Msg 306, Level 16, State 2, Line 61
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
So lets say in my previous example one of the SRC and DST columns is an ntext column. How could I account for that?
November 21, 2011 at 12:22 pm
for example, additional NTEXT field
INSERT INTO XDW.dbo.tbl_Xclaimflag
([NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) SELECT
[NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
FROM ( MERGE INTO XDW.dbo.tbl_Xclaimflag AS DST USING (
SELECT [NTEXT], Xclaimflag_id, MIN(warehoused) AS warehoused,Xclaim_id
,Xclaimflagtype_id
,value
FROM X.dbo.tbl_Xclaimflag
GROUP BY [NTEXT], Xclaimflag_id, Xclaim_id, Xclaimflagtype_id, value
)AS SRC ON (SRC.Xclaimflag_id = DST.Xclaimflag_id AND SRC.warehoused = DST.warehoused)
WHEN NOT MATCHED THEN INSERT
([NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) VALUES
([NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
) WHEN MATCHED AND DST.[Current] = 1 AND (ISNULL(SRC.[NTEXT],0) <> ISNULL(DST.[NTEXT],0)
OR ISNULL(SRC.[Xclaim_id],0) <> ISNULL(DST.[Xclaim_id],0)
OR ISNULL(DST.[Xclaimflagtype_id],0) <> ISNULL(SRC.[Xclaimflagtype_id],0)
OR ISNULL(DST.[value],'') <> ISNULL(SRC.[value],'')
) THEN UPDATE SET DST.[Current] = 0, DST.[EndEffectiveDate] = getDate() OUTPUT SRC.[Xclaimflag_id]
,SRC.[NTEXT]
,SRC.[Xclaim_id]
,SRC.[Xclaimflagtype_id]
,SRC.[value]
,SRC.[warehoused]
, $Action AS MergeAction ) AS MRG WHERE MRG.MergeAction = 'UPDATE'
November 21, 2011 at 2:15 pm
GOT IT! 😀
INSERT INTO XDW.dbo.tbl_Xclaimflag
([NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) SELECT
[NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
FROM ( MERGE INTO XDW.dbo.tbl_Xclaimflag AS DST USING (
SELECT CAST([NTEXT]as VARCHAR(5000)) as [NTEXT], Xclaimflag_id, MIN(warehoused) AS warehoused,Xclaim_id
,Xclaimflagtype_id
,value
FROM X.dbo.tbl_Xclaimflag
GROUP BY CAST([NTEXT]as VARCHAR(5000)), Xclaimflag_id, Xclaim_id, Xclaimflagtype_id, value
)AS SRC ON (SRC.Xclaimflag_id = DST.Xclaimflag_id AND SRC.warehoused = DST.warehoused)
WHEN NOT MATCHED THEN INSERT
([NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[BeginEffectiveDate]
,[current]
) VALUES
([NTEXT]
,[Xclaimflag_id]
,[Xclaim_id]
,[Xclaimflagtype_id]
,[value]
,[warehoused]
,[warehoused]
,1
) WHEN MATCHED AND DST.[Current] = 1 AND (ISNULL(CAST(SRC.[NTEXT] AS VarChar (5000)),0) <> ISNULL(CAST(DST.[NTEXT] AS VarChar (5000)),0)
OR ISNULL(SRC.[Xclaim_id],0) <> ISNULL(DST.[Xclaim_id],0)
OR ISNULL(DST.[Xclaimflagtype_id],0) <> ISNULL(SRC.[Xclaimflagtype_id],0)
OR ISNULL(DST.[value],'') <> ISNULL(SRC.[value],'')
) THEN UPDATE SET DST.[Current] = 0, DST.[EndEffectiveDate] = getDate() OUTPUT SRC.[Xclaimflag_id]
,SRC.[NTEXT]
,SRC.[Xclaim_id]
,SRC.[Xclaimflagtype_id]
,SRC.[value]
,SRC.[warehoused]
, $Action AS MergeAction ) AS MRG WHERE MRG.MergeAction = 'UPDATE'
November 21, 2011 at 2:46 pm
That might work ok, but be careful. Since the original field was NTEXT, it may contain Unicode characters that would be lost on conversion to VARCHAR; you're probably better off converting to NVARCHAR instead. Also, make sure that your source NTEXT column will never contain values longer than 5000 characters, otherwise, some of your data will be truncated on conversion.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply