Append if rows do not exists else update if exists (2 Viewers)

[jaryszek]

Ok Guys i have one more question.

I would like to create new recordset from t_AnimalInput (UPSERT table but with condition).

So in one sql to do:
1. Check if Index equals 1 or 2 and take only these rows to create upsert query
2. Check if rows exist in t_AnimalSource and append if not
3. If exists just update rows in AnimalInput (but only with index 1,2 or just moved records)

How can i do this?

 

[jaryszek]

I tried with:

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]
Where t_AnimalInput.[Index]=1

but it failed

 

[arnelgp]

It is just a name Microsoft gave to INSERT queries for no good reason.

Everyone is calling it append query not just microsoft?

 

[Galaxiom]

Everyone is calling it append query not just microsoft?

The keyword in the "Append" query is INSERT. Other queries are named for their key word. SELECT, DELETE UPDATE.

Calling an INSET an Append is considered an Access quirk in the wider database fraternity. Same with calling a table column a "field". Even the SQL in Access calls them Columns.

 

[isladogs]

I'm slightly confused by your requirements as described in post #1.
Assuming I understand it correctly, this should partly work:

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]
WHERE (((t_AnimalInput.Index)=1 Or (t_AnimalInput.Index)=2));

However, as written it won't append new records from t_AnimalSource as the won't have a record with corresponding index in r_AnimalInput.
Shouldn't the index field be in the source table?

 

[jaryszek]

thank you Colin,

you pointed me in proper direction!
Yes,

this would not append record so i have to do the solution in 2 queries: 1 to append and one to update rows...

Best,
Jacek

 

[isladogs]

You're very welcome. Good luck with the new baby due tomorrow I believe.