Append if rows do not exists else update if exists (1 Viewer)

[jaryszek]

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

ID	Animal	Color
1​	Dog	Green
2​	Cat	White

t_AnimalSource

ID	Animal	Color
1​	Dog	Green
2​	Cat	Black
3​	Mouse	Red

and now expexted result is:
ResultTable

ID	Animal	Color
1​	Dog	Green
2​	Cat	White
3​	Mouse	Red

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:

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

 

[arnelgp]

update t_animalInput As A right join t_animalSourcer As B On A.animal = B.animal
set A.Animal = B.Animal, A.Color = B.Color;

 

[jaryszek]

thank you arnelgp.

you would do this as second step after inserting rows?

Best,
Jacek

 

[arnelgp]

you do it Alone. it will Insert (if does not exists in t_animalInput) and/or update (if it does).

 

[jaryszek]

thank you very much let me test it

Jacek

 

[jaryszek]

ok somehing is not working.

I tried with access query design feature.

I tried with:

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:

Why?

Please help,
Jacek

 

[arnelgp]

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:

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];

 

[jaryszek]

Thank you,
works!

so why this error were occuring? Can you explain?

Best,
Jacek

 

[NauticalGent]

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...

 

[Minty]

@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.

 

[NauticalGent]

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.

 

[theDBguy]

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.

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will...

thedbguy.blogspot.com

 

[NauticalGent]

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...

 

[isladogs]

@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

In fact, I'm fairly sure I told @jaryszek about it a year or two ago

 

[jaryszek]

Yes i know Colin about your side, thanks for reminding

Thank you Guys for help!
Jacek

 

[isladogs]

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.

 

[jaryszek]

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

 

[isladogs]

You're welcome Jacek. Enjoy being a parent. They grow up far too fast!

 

[Galaxiom]

@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.

 

[jaryszek]

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