• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

access / msmsql update query fails

Hello,

I have this sql update that does not work.  The dbo table is ms sql, and tblMembers is local access.
Error:  Syntax Error (missing operator)


UPDATE dbo_Customer
SET dbo_Customer.secFirstName = tblMembers.secondaryfirstname
INNER JOIN tblMembers
ON dbo_Customer.MembershipID = cstr([tblMembers].[Member_ID])
Where dbo_Customer.customerID=59447;

i figure it has to be simple, but i have fiddled with this for too long.
0
pressMac
Asked:
pressMac
  • 4
  • 3
1 Solution
 
Kelvin SparksCommented:
Try

UPDATE dbo_Customer
SET dbo_Customer.secFirstName = tblMembers.secondaryfirstname
FROM dbo.Customers
INNER JOIN tblMembers
ON dbo_Customer.MembershipID = cstr([tblMembers].[Member_ID])
Where dbo_Customer.customerID=59447;

Kelvin
0
 
pressMacAuthor Commented:
It still complains of syntax error.

Press
0
 
mbizupCommented:
Give this a shot:

UPDATE dbo_Customer, tblMembers
SET dbo_Customer.secFirstName = tblMembers.secondaryfirstname
WHERE dbo_Customer.MembershipID = cstr([tblMembers].[Member_ID])
AND dbo_Customer.customerID=59447;

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
pressMacAuthor Commented:
Works great.  Did not occur to me to fore go the join all together.

Press
0
 
pressMacAuthor Commented:
Worked great
0
 
mbizupCommented:
Fwiw, I think that Access joins need to be directly on the fields,  not functions.  I don't have a database handy to verify that, though.
0
 
pressMacAuthor Commented:
more fwiw, the select statement with that join does work.
0
 
mbizupCommented:
Thanks for checking that.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now