update the tbl

[Machlink]

I have following query to update tbl materail

UPDATE Material RIGHT JOIN Engcost ON Material.[PART NO]=Engcost.[PART NO] SET Engcost.[PART NO] = Material.[PART NO], Engcost.[MATERIAL DESCRIPTION] = Material.[MATERIAL DESCRIPTION], Engcost.[COST EACH] = Material.[COST EACH], Engcost.CD = Material.CD, Engcost.STORELOC = Material.STORELOC
WHERE (((Engcost.[PART NO]) Like "TD*" Or (Engcost.[PART NO])="CM00049"));

Material tbl is updating only matching recordsof Engcost. I want to update material all the TD parts even with match or without match.

Thanks in advance

 

[rac2]

UPDATE Material
RIGHT JOIN Engcost ON Material.[PART NO]=Engcost.[PART NO] 

SET
  Engcost.[PART NO] = Material.[PART NO],
  Engcost.[MATERIAL DESCRIPTION] = Material.[MATERIAL DESCRIPTION],
  Engcost.[COST EACH] = Material.[COST EACH],
  Engcost.CD = Material.CD,
  Engcost.STORELOC = Material.STORELOC

WHERE
  (Engcost.[PART NO] Like "TD*"
  Or
  Engcost.[PART NO]="CM00049");

I re-arranged the query just to make it easier for me to read. The extra parentheses in the WHERE clause were not needed.

Setting Engcost.[PART NO] = Material.[PART NO] does nothing; the two columns are already equal in every row because that is the JOIN condition. That is how you are matching rows from the two tables.

SET Engcost.CD = Material.CD means to change the value of the CD column in the Engcost table, replace it with the value in the Material table. Which may be the opposite of what you want to do. If you want the values in the Material table to get the values from the Engcost table you would SET Material.CD = Engcost.CD.

Possibly the SQL statement was generated for you by Access. And possibly you created the JOIN using the GUI to make the link, then right-clicked on the link and used the dialog to produce the RIGHT JOIN. Maybe that is how the order of the items in the SET clause came out backwards.

In any case, to UPDATE the Materials table try a simple JOIN like so-

UPDATE Material
JOIN Engcost ON Material.[PART NO]=Engcost.[PART NO] 

SET
  Material.[PART NO] = Engcost.[PART NO],
  Material.[MATERIAL DESCRIPTION] = Engcost.[MATERIAL DESCRIPTION],
  Material.[COST EACH] = Engcost.[COST EACH],
  Material.CD = Engcost.CD,
  Material.STORELOC = Engcost.STORELOC

WHERE
  (Material.[PART NO] Like "TD*"
  Or
  Material.[PART NO]="CM00049");

Think of this query as first retrieving the rows in the Materials table with part numbers like TD or equal to CM00049. That is the WHERE clause.

Then for each one of those rows, find the matching row in the Engcost table. Match them on part no. That is the JOIN clause.

Then take the values from the Engcost row and update the the matching row in Materials. That is the SET clauses.


Hope this helps.