0

I have the following SQL...

DECLARE @FromDate DATETIME
   ,@toDate DATETIME

IF @toDate IS NULL
  SET @toDate = GETDATE()

IF @FromDate IS NULL
  SET @FromDate = DATEADD(MONTH, -13, @toDate)

SET @FromDate = LEFT(CONVERT(VARCHAR, @FromDate, 121), 10) + ' 00:00:00'
SET @toDate = LEFT(CONVERT(VARCHAR, @toDate, 121), 10) + ' 23:59:59'

;WITH CrossTable_Target (Firm, MonthYearDate, Total)
AS (SELECT
    Firms.Name AS Firm
   ,TimeDimension.DATE AS MonthYearDate
   ,0 AS Total
  FROM dbo.Firms
  CROSS JOIN dbo.TimeDimension
  WHERE TimeDimension.Day_Number_Of_Month = 1
  AND TimeDimension.DATE >= @FromDate
  AND TimeDimension.DATE <= @toDate),
    ValuesTable_Source
AS (SELECT
    subquery.Firm
   ,TimeDimension.DATE AS MonthYearDate
   ,COUNT(subquery.Firm) AS Total
  FROM (SELECT
      Firms.Name AS Firm
     ,DATEADD(MONTH, DATEDIFF(MONTH, 0, Incidents.LoggedDate), 0) AS MonthYearDate
 ,Incidents.LoggedDate
FROM dbo.Incidents
RIGHT OUTER JOIN dbo.Firms
  ON Incidents.FirmId = Firms.Id
WHERE Incidents.StatusId >= 4
AND Incidents.IsDisabled = 0
GROUP BY Incidents.LoggedDate
        ,Firms.Name) subquery
  RIGHT OUTER JOIN dbo.TimeDimension
    ON subquery.MonthYearDate = TimeDimension.DATE
  WHERE TimeDimension.Day_Number_Of_Month = 1
  AND TimeDimension.DATE >= @FromDate
  AND TimeDimension.DATE <= @toDate
  AND subquery.Firm IS NOT NULL
  GROUP BY subquery.Firm
      ,TimeDimension.DATE)

MERGE CrossTable_Target AS Target
USING (SELECT
    *
  FROM ValuesTable_Source) AS Source
ON (
  Target.MonthYearDate = Source.MonthYearDate
  AND Target.Firm = Source.Firm
  )
WHEN MATCHED
  THEN UPDATE
    SET Target.Total = Source.Total;   


CTE CrossTable_Target...
Firm  EntryDate  Total
aaa   01/06/2015 0
bbb   01/06/2015 0
ccc   01/06/2015 0
ddd   01/06/2015 0
aaa   01/07/2015 0
bbb   01/07/2015 0
ccc   01/07/2015 0
ddd   01/07/2015 0
aaa   01/08/2015 0
bbb   01/08/2015 0
ccc   01/08/2015 0
ddd   01/08/2015 0

CTE ValuesTable_Source...
Firm  EntryDate  Total
aaa   01/06/2015 3
aaa   01/07/2015 6
ccc   01/07/2015 2
ddd   01/07/2015 0
aaa   01/08/2015 0
bbb   01/08/2015 0

I want to be able to update/replace the Totals column for each row on the CrossTable_Target with a Totals value from the ValuesTable_Source whenever the EntryDate and Firm name matches.

Unfortunately I am getting the followiwng error...

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.

I would be really grateful if someone could point out to me where/how I can get this working.

Am I using the SQL MERGE Correctly, or should I be using something like a loop with an UPDATE ?

  • 2
    Besides the rather obvious error message, the code seems to be trying to update MonthYearDate and Firm which come from different base tables. This is not allowed in SQL Server. You cannot update 2 tables in a single statements. You need 2 statements for that. And then it would try to update Total which is a computed column and not a column of any base table. This is just not possible. – ypercubeᵀᴹ Jun 25 '16 at 17:21
  • 1
    Makes me wonder wether you really want to update some tables or a simple select query. – ypercubeᵀᴹ Jun 25 '16 at 17:26
0

Before you vote this down look at the question before the major edits
This is valid (and good) answer to the original stated question

What you have is just messed up

Why use a merge for an update only?

Why use a CTE when it is the whole table

update firms
set firms.Total = results.Total
from firms 
join results 
      on firms.firm = results.firm 
     and firms.Total <> results.Total 
join TimeDimension 
      on TimeDimension.EntryDate = results.EntryDate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.