I am attempting to use a MERGE statement to update a target table from a source table but am getting an error because SQL is trying to UPDATE or DELETE more than one record. For background - I have 'source' table that is essentially a rolling table that records every instance of the data the business adds - it is only keyed by an auto increment 'record id'. The 'target' table can only have once instance of the primary key - which is 'string'. The idea is to look to the source table - if the primary key is matched, then UPDATE said rows with the new data, if not - INSERT all of the information from the new rows. The issue is I keep getting the error that I am trying to UPDATE or DELETE the same record twice. I believe this is happening because there is indeed TWO (or more) instances of the string (primary key) in the source table. The only difference is the 'date_added' field. How can I re-write this to incorporate both? I am very new to this SQL function and I have tried a few things but all return errors. All credit to another user who gave me the suggestion of MERGE in the first place - I attempting to use an IF/THEN.
I tried using the MAX record date in the ON and MATCH clause both of which returned errors due to syntax
MERGE
SCM_Top_Up_Operational O
USING SCM_Top_Up_Rolling R ON (O.String = R.string)
WHEN MATCHED
THEN UPDATE SET
O.Date_Added = R.Date_Added,
O.Real_Exfact = R.Real_Exfact,
O.Excess_Top_Up = R.Excess_Top_Up
WHEN NOT MATCHED BY TARGET
THEN INSERT ( String, Date_Added, Real_Exfact, Article_ID, Excess_Top_Up, Plant)
VALUES (R.String, R.Date_Added, R.Real_Exfact, R.Article_ID, R.Excess_Top_Up, R.Plant);
Here is some sample data. If I query scm_top_up_rolling for string in ('B418496220','B111116220') I get these results:
RECORD_ID String Date_Added Real_Exfact Article_ID Excess_Top_Up Plant
----------- ---------- ---------- ----------- ---------- --------------------------------------- -----
3108 B418496220 2019-02-25 2019-05-15 B41849 1235 6220
3211 B418496220 2019-03-28 2019-03-28 B41849 1 6220
3212 B111116220 2019-03-28 2019-03-28 B11111 1 6220
Now if I query scm_top_up_operational for the same strings I get:
String Date_Added Real_Exfact Article_ID Excess_Top_Up Plant
---------- ---------- ----------- ---------- --------------------------------------- -----
B418496220 2019-02-25 2019-05-15 B41849 1235 6220
My goal would be for scm_top_up_operational to be UPDATED with the most recent entry for B418496220 since it already exists in the operational table. Then I would like to INSERT the new record for B111116220 as it doesn't exist in the operational table.
Hope that helps and thanks.
You cannot use SCM_Top_Up_Rolling directly in the merge statement as the key (i.e. String) used for the join needs to be unique.
What you need to do is to prepare the source data by adding a few layers of CTE (Common Table Expression) on top of the merge statement. The aim of this step is to remove the duplicates and return a unique list of rows.
Take a look at the solution below:
;with cte
as
(
select String, Date_Added, Real_Exfact, Article_ID, Excess_Top_Up, Plant
, row_number() over (partition by String order by Date_Added desc) as 'rank'
from dbo.SCM_Top_Up_Rolling
)
, cte_source
as
(
select *
from cte
where rank = 1
)
merge SCM_Top_Up_Operational O
using cte_source R on (O.String = R.String)
when matched then
update set
O.Date_Added = R.Date_Added,
O.Real_Exfact = R.Real_Exfact,
O.Excess_Top_Up = R.Excess_Top_Up
when not matched by target then
insert ( String, Date_Added, Real_Exfact, Article_ID, Excess_Top_Up, Plant)
values (R.String, R.Date_Added, R.Real_Exfact, R.Article_ID, R.Excess_Top_Up, R.Plant);
The key component of the query is the window function:
row_number() over (partition by String order by Date_Added desc) as 'rank'
which generates a new column rank:
String Date_Added rank
----------------------------
B418496220 2019-02-25 2
B418496220 2019-03-28 1 <= To be used in the merge, e.g. where rank = 1
B111116220 2019-03-28 1 <= To be used in the merge, e.g. where rank = 1
Once we've produced a unique list (i.e. a list of Strings with the latest Date_Added), we can then use this list as our source for the merge.
merge SCM_Top_Up_Operational O
using cte_source R on (O.String = R.String)
Hope the answer is clear enough. Good luck.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다