February 28, 2022 at 8:04 pm
I have a table with a column named JDX that I wish to merge into and I want to limit the merge considerations to the set where JDX is 180. I reduced the fields being updated for brevity. It works on the "matched " and "not matched by Source" by adding "and jdx = 180" but the "not matched by Target" fails if there is a row with a different value in the JDX column but the same TaxID. "and jdx = 180" seemed wrong and now I see why. Is there a way to add something to the "Merge into" section? How do I limit the target for consideration?
Thanks!
Merge into Accounts a
Using TTN5563 t
On t.TaxID = a.TaxID
When matched and jdx = 180
Then Update
Set
a.PropID = t.PropID
When not matched by Target
Then Insert (Jdx, PropID)
Values(180, PropID)
When not matched by Source and jdx = 180
Then Delete;
February 28, 2022 at 8:55 pm
I think I figured it out.
Merge into Accounts a
Using TTN5563 t
On t.TaxID = a.TaxID
And a.jdx = 180
February 28, 2022 at 9:08 pm
That didn't work I removed the "and jdx = 180" from the "When not matched by Source" portion and it deleted rows that were not 180.
February 28, 2022 at 9:24 pm
Solved with a CTE.
With AcctTrav as (Select * from Accounts where Jdx = 180)
Merge into AcctTrav a
Using TTN5563 t
On t.TaxID = a.TaxID
March 1, 2022 at 9:51 am
Solved with a CTE.
With AcctTrav as (Select * from Accounts where Jdx = 180)
Merge into AcctTrav a
Using TTN5563 t
On t.TaxID = a.TaxID
Nice. Well done on posting the evolution of this problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply