Merge into a subset of the target

Question

Merge into a subset of the target

CAJ

SSC Enthusiast

Points: 115
More actions
February 28, 2022 at 8:04 pm

#3990696
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;
```

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

CAJ SSC Enthusiast Points: 115 More actions · Answer 1

I think I figured it out.

Merge into Accounts a
Using TTN5563 t
On t.TaxID = a.TaxID 
And a.jdx = 180

CAJ SSC Enthusiast Points: 115 More actions · Answer 2

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.

CAJ SSC Enthusiast Points: 115 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246733 More actions · Answer 4

CAJ wrote:

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.