UPDATE Query from a UNION Query source in T-SQL

[NightZEN]

Hi, I am having trouble figuring out how to do this, I am a bit green so there may be a better way than my approach. I have a series of queries (2) in an Access Front-End that are quite slow. The first is a UNION that joins 2 views, the second is an update that sets a flag in a table. I first tried to duplicate the UNION query in a view like so, but apparently views do not support Unions(?)

SELECT Name, MaxOfDateQC
FROM vw_vendor_current_mech
UNION ALL
SELECT Name, MaxOfDateQC
FROM vw_vendor_current_elec;



I then tried the above in Query builder, and it works fine. The second query, however, is an UPDATE that uses this UNION as its source. Ideally I could put this all in a stored proceedure that could run when my form opens. This is in essencse what I want to do, but it doesn't work:

UPDATE TblVendors SET TblVendors.Vendor = 1
WHERE TblVendors.Name IN (SELECT [Name] FROM

(SELECT Name, MaxOfDateQC
FROM vw_vendor_current_mech
UNION ALL
SELECT Name, MaxOfDateQC
FROM vw_vendor_current_elec);

Thanks for your help.

 

[ProdAir]

I believe you need to give your union query a name i.e.

(SELECT Name, MaxOfDateQC
FROM vw_vendor_current_mech
UNION ALL
SELECT Name, MaxOfDateQC
FROM vw_vendor_current_elec) AS tab1