Append if rows do not exists else update if exists

jaryszek

Registered User.
Local time
Today, 15:29
Joined
Aug 25, 2016
Messages
756
Hi Guys,

long time since i have been here (we moved to Excel entirely :( )

i have 2 tables:
ID is a key for us.

t_AnimalInput

IDAnimalColor
1​
DogGreen
2​
CatWhite
t_AnimalSource

IDAnimalColor
1​
DogGreen
2​
CatBlack
3​
MouseRed
and now expexted result is:
ResultTable

IDAnimalColor
1​
DogGreen
2​
CatWhite
3​
MouseRed
Sp User inputted something in t_AnimalInput table.
And i want to check if in t_AnimalSource there is a record which does not exists and if not, append to t_AnimalInputs.


So i have used this SQL:

Code:
INSERT INTO t_AnimalInput ( ID, Animal, Color )
SELECT t_AnimalSource.ID, t_AnimalSource.Animal, t_AnimalSource.Color
FROM t_AnimalSource LEFT JOIN t_AnimalInput ON t_AnimalSource.ID = t_AnimalInput.ID
WHERE (((t_AnimalInput.ID) Is Null));

which is working perfect.

But how to check if there is a still the same records in both tables and if they have the same keys - update them?
As you can see ID = 2 is different because Color changed from Black to White.

It is better to use second SQL somehow or VBA after first insert SQL?

I think that SQL solution is more desired. Because Access VBA <> Excel VBA and i can be force to use also these kind of operations in Excel in the future.

Please help,
thank you in advance
Jacek
 

Attachments

thank you arnelgp.

you would do this as second step after inserting rows?

Best,
Jacek
 
you do it Alone. it will Insert (if does not exists in t_animalInput) and/or update (if it does).
 
ok somehing is not working.

I tried with access query design feature.

I tried with:

Code:
UPDATE t_AnimalInput RIGHT JOIN t_AnimalSource ON t_AnimalInput.ID = t_AnimalSource.ID SET t_AnimalInput.Animal = [t_AnimalSource].[Animal], t_AnimalInput.Color = [t_AnimalSource].[Color];

and i am getting:
1636025708699.png


Why?

Please help,
Jacek
 

Attachments

modify both your table.

t_animalInput and t_animalSource, ID field should be set Autonumber.

make animal field of t_animalInput as Primary key.
make ID field of t_animalSource as Primary Key.

join them in the query using animal field:
Code:
UPDATE t_AnimalSource
    LEFT JOIN t_AnimalInput ON
        t_AnimalSource.Animal = t_AnimalInput.Animal
SET t_AnimalInput.Animal = [t_AnimalSource].[Animal],
t_AnimalInput.Color = [t_AnimalSource].[Color];
 

Attachments

Thank you,
works!

so why this error were occuring? Can you explain?

Best,
Jacek
 
I am in complete shock. Up until now I was CERTAIN this could only be done with two queries. Going to test this immediatly. Talk about a real tool to add to the toolbox...
 
@NauticalGent It is known as an Upsert Query, quite a nifty trick.

If you are on SQL Server you can use a Merge statement with some clever switches to do the same, including deletes.
Now that is super clever.
 
It is known as an Upsert Query, quite a nifty trick.
Upsert Query - I like it! WHY is this not more widely known? The white-lab coat wearing MS Access twits team members should have included this in the Query Wizard.

This is HUGE. I cant believe I haven't stumbled across this before now and I have an even harder time believing that of all the sites I frequent, NO ONE has this is their code snippets.

Rant over, time for a single malt and then some code re-writes.
 
I am in complete shock. Up until now I was CERTAIN this could only be done with two queries. Going to test this immediatly. Talk about a real tool to add to the toolbox...
I guess that means you haven't seen this before.
 
Well now I stand corrected and I must admit you were one of the sites I was referring to! Please accept my humble apology and if you hurry, I'll pour a three-finger dram for you. If you cannot make it, I'll drink it for you and flog myself mentally for falsely accusing you...

I KNOW you just added that and changed the date to shame me...
 
Yes i know Colin about your side, thanks for reminding :)

Thank you Guys for help!
Jacek
 
Hi @jaraszek
Side? Did you mean site?
Anyway haven't seen you here for a while. Hope you are doing well. IIRC you had just become a father a year or so ago.
 
Tjhank you Colin, o wow you have very good memory, i ma jelous - when i learned some technical solution when i am not using...i am forgetting all :(

Yes i became father and in 5 days i will be second time! :) Just using excel all the time, access not now but i learned a lot from you and other Guys here :)
Thank you from my heart.

Best Wishes for you and your family!
Best,
Jacek
 
You're welcome Jacek. Enjoy being a parent. They grow up far too fast!
 
@NG
As you know I call it an UPEND query as I prefer that to UPSERT but its exactly the same thing.
I wrote about it here UpEnd Query - Mendip Data Systems and have also referred to it in many answers here on this forum
I prefer UPSERT. UPDATE and INSERT.

APPEND queries don't actually exist. It is just a name Microsoft gave to INSERT queries for no good reason. We APPEND Fields and Parameters not records.
 
I prefer UPSERT. UPDATE and INSERT.

APPEND queries don't actually exist. It is just a name Microsoft gave to INSERT queries for no good reason. We APPEND Fields and Parameters not records.
What is the difference?

Best,
Jacek
 

Users who are viewing this thread

Back
Top Bottom