Update Query - Syntax Error in Update Statement

[Nogi]

I'm a bit stuck on this problem i'm having with my update-query.

I've tried my best to write an sql query to update the fields of one table with the value of fields in another table, but i keep on receiving this annoying "syntax error in update statement".

Is there somebody who can help me out with this issue please?

This is what i've accomplished so far:

UPDATE SortedFullExcelList p
SET p.Itemnumber = (SELECT n.Itemnumber FROM FullExcelList n WHERE n.id = p.id),
SET p.ItemDescription = (SELECT n.ItemDescription FROM FullExcelList n WHERE n.id = p.id),
SET p.ItemType = (SELECT n.ItemType FROM FullExcelList n WHERE n.id = p.id),
SET p.Download = (SELECT n.Download FROM FullExcelList n WHERE n.id = p.id)

Uhm...any idea's?

 

[PHV]

But in your example where is the INVARIANT key field needed to know that "swimming" WAS "football" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Nogi]

oh..uhm..to be honest it's the first time i hear this word "invariant key field".

I maybe came up with another idea that is maybe easier to create:

Wouldn't it be a solution to work with a delete and append-query?

First i run a query to delete all records from the "updated shop" table, and then append all records from the "shop" table into the "updated shop"-table?

That way after the query has run the tables are exactly the same.

Afterwards i can still run another query to make the values unique, and that's it.

Or wont that work?

 

[greekpatriott]

Yes this is what I do as well, I create two queries, a delete table query and an append query. Then I use a macro on a form to run these queries. I am sure though there exists another way through sql.