July 12, 2022 at 5:12 pm
Hello everyone,
Needed to help with a merge statement. So based on what I've read about merge statements the syntax on this should be correct, but it's not. So my statement is below:
merge dbo.qsr_store store
using dbo.qsr_store_staging staging on store.id = staging.id and store.brand_code = staging.brand_code
when matched then
update set store.golive_date = staging.golive_date, store.update_datetime = getdate(), store.store_name = staging.store_name, store.store_code = staging.store_code
where store.golive_date <> staging.golive_date or store.store_name <> staging.store_name
when not matched then
insert (id, brand_code, brand_name, store_code, store_name, golive_date, close_date, reporting_frequency_id, reporting_frequency_desc, manual_reported)
values (id, brand_code, brand_name, store_code, store_name, golive_date, close_date, reporting_frequency_id, reporting_frequency_desc, manual_reported)
;
The where clause on the update portion is where the issue is. Works when I remove the where clause, but of course I don't want to update the whole set. Can anyone see what I'm doing wrong here?
Thanks,
James
July 12, 2022 at 8:46 pm
Does this work?
merge dbo.qsr_store store
using dbo.qsr_store_staging staging on store.id = staging.id and store.brand_code = staging.brand_code
when matched and (store.golive_date <> staging.golive_date or store.store_name <> staging.store_name) then
update set store.golive_date = staging.golive_date, store.update_datetime = getdate(), store.store_name = staging.store_name, store.store_code = staging.store_code
--where store.golive_date <> staging.golive_date or store.store_name <> staging.store_name
when not matched then
insert (id, brand_code, brand_name, store_code, store_name, golive_date, close_date, reporting_frequency_id, reporting_frequency_desc, manual_reported)
values (id, brand_code, brand_name, store_code, store_name, golive_date, close_date, reporting_frequency_id, reporting_frequency_desc, manual_reported)
;
July 13, 2022 at 3:11 pm
I actually tried it that way prior to the where clause way. It does work, but the update will update all the matched records since there is no filter on the other fields.
July 13, 2022 at 3:47 pm
I actually tried it that way prior to the where clause way. It does work, but the update will update all the matched records since there is no filter on the other fields
Why do you say there is no filter on the other fields? The filter was not removed, it was moved to and () statement after "when matched" --
when matched and (store.golive_date <> staging.golive_date or store.store_name <> staging.store_name) then
That is proper MERGE syntax (see example D). WHERE clause is not supported.
July 13, 2022 at 3:47 pm
I actually tried it that way prior to the where clause way. It does work, but the update will update all the matched records since there is no filter on the other fields.
The filter is in the WHEN MATCHED part.
I wrote some code to create your tables with a subset of the columns and I thought it was updating all of the rows at first, but it was because I used a default of getdate() for inserting update_datetime, so the initial inserts and the updates were only milliseconds apart. When I set the update_datetime to a year ago it became easier to see that only changed and new rows had an update_datetime of just now.
DROP TABLE IF EXISTS dbo.qsr_store_staging;
CREATE TABLE dbo.qsr_store_staging
( id INT,
brand_code VARCHAR(100),
store_code VARCHAR(10),
store_name VARCHAR(100),
golive_date DATETIME
);
DROP TABLE IF EXISTS dbo.qsr_store;
CREATE TABLE dbo.qsr_store
( id INT,
brand_code VARCHAR(100),
store_code VARCHAR(10),
store_name VARCHAR(100),
golive_date DATETIME,
update_datetime DATETIME DEFAULT(getdate())
);
DECLARE @previousdate datetime = DATEADD(YEAR,-1,GETDATE())
INSERT dbo.qsr_store (id, brand_code, store_code, store_name, golive_date, update_datetime)
VALUES (1, 'A', 'A1', 'AAA', '2022-01-01', @previousdate),
(2,'B', 'BBB', 'B1', '2022-01-01', @previousdate),
(3,'C', 'CCC', 'C1', '2022-01-01', @previousdate),
(4,'D', 'DDD', 'D1', '2022-01-01', @previousdate),
(5,'E', 'EEEE', 'E1', '2022-01-01', @previousdate);
--SELECT *
--FROM dbo.qsr_store;
INSERT dbo.qsr_store_staging
VALUES (1, 'A', 'A1', 'AAAAAA', '2022-01-01'),
(2,'B', 'BBB', 'B1', '2022-06-01'),
(3,'C', 'CCC', 'C1', '2022-01-01'),
(4,'D', 'DDD', 'D1', '2022-01-01'),
--(5,'E', 'EEEE', 'E1', '2022-01-01'),
(6,'F', 'FFF', 'F1', '2022-07-01'),
(7,'E', 'GGG', 'G1', '2022-07-01');
--SELECT *
--FROM dbo.qsr_store_staging;
merge dbo.qsr_store store
using dbo.qsr_store_staging staging on store.id = staging.id and store.brand_code = staging.brand_code
when matched and (store.golive_date <> staging.golive_date or store.store_name <> staging.store_name) then
update set store.golive_date = staging.golive_date, store.update_datetime = getdate(), store.store_name = staging.store_name, store.store_code = staging.store_code
when not matched then
insert (id, brand_code, store_code, store_name, golive_date, update_datetime)
values (id, brand_code, store_code, store_name, golive_date, GETDATE())
;
SELECT *
FROM dbo.qsr_store
WHERE update_datetime > GETDATE()-60;
SELECT *
FROM dbo.qsr_store
WHERE update_datetime < GETDATE()-60;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply