Update Query using a where clause

[NightZEN]

I am trying to construct a query to set a "flag" (bit data type field) if the name column matches any name in another query. The othere query happens to be a union. This is what I am thinking, though it doesn't work:

UPDATE qry_vendor_current_both INNER JOIN TblVendors ON qry_vendor_current_both.Name = TblVendors.Name
SET TblVendors.Vendor = 0
WHERE (((TblVendors.Name)=[qry_vendor_current_both].[Name]));

Basically I want to set the flag in TblVendors.Vendor if TblVendors.Name is equal to any qry_vendor_current_both.Name

The error says "The operation must use an updatable Query", presumably because qry_vendor_current_both is a Union. I can do this in vba, but its slow. Any Idea's would be great!!

Thanks!
Keith

 

[traingamer]

What about:

UPDATE [COLOR=red]TblVendors[/color] INNER JOIN [COLOR=red]qry_vendor_current_both[/color] ON qry_vendor_current_both.Name = TblVendors.Name
SET TblVendors.Vendor = 0
WHERE (((TblVendors.Name)=[qry_vendor_current_both].[Name]));

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[BSman]

Try this:

UPDATE TblVendors SET TblVendors.Vendor = 0
WHERE TblVendors.Name IN (SELECT [Name] FROM [qry_vendor_current_both]);

 

[NightZEN]

beauty, BSman

Thanks!