SQL update from one Table to another based on a ID match

[marsheng]

I've looked on the web and tried many options but I sill cannot get this right.

I tried using the query builder, but that too gave errors. This is what I want to do.

UPDATE members
SET members.MNZID = mnz_members.MNZID
WHERE members.MemID = mnz_members.MemID

Unknown column mnz_members.MemID
I do have the capitals correct.

[marsheng]

For some reason, nearly every web example and solution provided only has one table listed in the update clause.

If you add both tables, then it works. !!!!!

UPDATE members , mnz_members
SET members.MNZID = mnz_members.MNZID
WHERE members.memID=mnz_members.memID

Most confused as I'm not updating mnz_members.

[alexa]

We are currently reviewing this post and will answer you as soon as possible.

[alexa]

The query should look as follows:

Code: Select all

UPDATE
  m
SET
  m.MNZID = mnz_m.MNZID
FROM
  members m
JOIN
  mnz_members mnz_m
ON
  m.memID=mnz_m.memID

Please also see the following https://msdn.microsoft.com/en-us/library/ms177523.aspx