What's wrong with this statement

update members set members.print_card='PR'  from        Members INNER JOIN
                      Sales  ON Members.MemberID = Sales.MemberID
where     (Members.print_batch = '9077') AND (Sales.Batch_Nu = '8906')

I want to update the print function to 'PR' in members where the field print_batch is 9077 in members and batch_nu in sales is 8906. The two tables have memberid as the related field. It keeps trying to do a cross join which is not what I want.

Try using a MERGE command instead.

;with cte as (select * from Members where Print_Batch = '9077')

Merge cte as M

using (select * from Sales where Batch_Nu = '8906') S ON M.MemberId = S.MemberId

WHEN Matched THEN UPDATE

SET Print_Card = 'PR'

-------------

Most likely you have multiple sales for each memberID in that batch. In this case you'll get an error.

So, you can re-write your statement this way:

UPDATE MEMBERS SET Print_Card = 'PR'

WHERE Print_Batch = '9077' and exists (select 1 from Sales S where S.MemberId = Members.MemberId and S.Batch_NU = '8906')

If you want to set that print_card as long as you have such Sales for the Member.

What's wrong with this statement

update members set members.print_card='PR'  from        Members INNER JOIN
                      Sales  ON Members.MemberID = Sales.MemberID
where     (Members.print_batch = '9077') AND (Sales.Batch_Nu = '8906')

I want to update the print function to 'PR' in members where the field print_batch is 9077 in members and batch_nu in sales is 8906. The two tables have memberid as the related field. It keeps trying to do a cross join which is not what I want.

Try this first to see if it's what you want:

SELECT m.print_card  
FROM Members m
INNER JOIN Sales s ON m.MemberID = s.MemberID and s.Batch_Nu = '8906'
WHERE m.print_batch = '9077' 

If that doesn't yield what you expected then you'll need to rethink the query all together.

UPDATE m 
SET m.print_card = 'PR'  
FROM Members AS m
INNER JOIN Sales AS s ON m.MemberID = s.MemberID
WHERE m.print_batch = '9077' AND s.Batch_Nu = '8906'

I used the second suggestion and it worked fine.