Update query

[samotek via AccessMonster.com]

I want to build a query where the data from the table products1 to be
inserted into the table products.
I have made he following query which does not update :
UPDATE products INNER JOIN products1 ON products.Productid = products1.
Productid SET products.branch5 = [products1].[branch5];
The figures in table products do not show the figures from tble products1.
Why is it so ?

 

[Wolfgang Kais]

Hello "samotek".

I want to build a query where the data from the table products1
to be inserted into the table products.
I have made he following query which does not update :
UPDATE products INNER JOIN products1 ON products.Productid =
products1.Productid SET products.branch5 = [products1].[branch5];
The figures in table products do not show the figures from tble
products1. Why is it so ?

What your query does is to overwrite the content of the branch5
field in the ptoducts table with the content of the branch5 field
from the products1 table when both records contain the same
Productid.
What you seem to expect is an insert query like this:
INSERT products (branch5) SELECT brach5 FROM products1;
Or you could give this one a try, which can update and insert at
the same time, unless propductID is an autonumber field:
UPDATE products RIGHT OUTER JOIN products1
ON products.Productid = products1.Productid
SET products.branch5 = products1.branch5,
products.ProductID = products1.ProductID

 

[samotek via AccessMonster.com]

Thank you for your reply.Productsid are autonumber fields in both tables.With
the first example i get syntax error. What is my mistake ?

Hello "samotek".

I want to build a query where the data from the table products1
to be inserted into the table products.

[quoted text clipped - 3 lines]

The figures in table products do not show the figures from tble
products1. Why is it so ?

What your query does is to overwrite the content of the branch5
field in the ptoducts table with the content of the branch5 field
from the products1 table when both records contain the same
Productid.
What you seem to expect is an insert query like this:
INSERT products (branch5) SELECT brach5 FROM products1;
Or you could give this one a try, which can update and insert at
the same time, unless propductID is an autonumber field:
UPDATE products RIGHT OUTER JOIN products1
ON products.Productid = products1.Productid
SET products.branch5 = products1.branch5,
products.ProductID = products1.ProductID

 

[Wolfgang Kais]

Sorry:
INSERT INTO products (branch5) SELECT branch5 FROM products1;

--
Regards,
Wolfgang

Thank you for your reply.Productsid are autonumber fields in both tables.With
the first example i get syntax error. What is my mistake ?

Hello "samotek".

I want to build a query where the data from the table products1
to be inserted into the table products.

[quoted text clipped - 3 lines]

The figures in table products do not show the figures from tble
products1. Why is it so ?

What your query does is to overwrite the content of the branch5
field in the ptoducts table with the content of the branch5 field
from the products1 table when both records contain the same
Productid.
What you seem to expect is an insert query like this:
INSERT products (branch5) SELECT brach5 FROM products1;
Or you could give this one a try, which can update and insert at
the same time, unless propductID is an autonumber field:
UPDATE products RIGHT OUTER JOIN products1
ON products.Productid = products1.Productid
SET products.branch5 = products1.branch5,
products.ProductID = products1.ProductID