UPDATE where INNER JOIN is needed
December 4th, 2008 by admin | No Comments | Filed in DatabaseHow does one UPDATE a field in a table where the key provided is in another table?
It is actually pretty simple:
UPDATE alias1 SET alias1.MyField = 'NEWDATA' FROM tblTable1 alias1 INNER JOIN tblTable2 alias2 ON alias1.ProductId = alias2.ProductId WHERE alias2.PresentationId = '911'
I hope this makes sense. It is easy to test before running the update by simply replacing the UPDATE with a SELECT for testing to make sure the query is returning the correct data.
SELECT * FROM tblTable1 alias1 INNER JOIN tblTable2 alias2 ON alias1.ProductId = alias2.ProductId WHERE alias2.PresentationId = '911'
If you know a better way to accomplish this, please leave me a comment on this post.