update a set a.Status = case when b.ProductsName is null then 0 else 1 end from b inner join c on b.customerId=c.customerId right join a on a.ProductsName=b.ProductsName and a.Trade=b.Trade and a.ProductsModel=b.ProductsModel and a.CompanyName=c.CompanyName
UPDATE a SET status = 1 WHERE EXISTS ( SELECT 1 FROM ( SELECT * FROM b INNER JOIN c ON b.customerid = c.customerid ) AS t WHERE a.productsname = t.productsname AND a.trade = t.trade AND a.productsmodel = t.productsmodel AND a.companyname = t.companyname )
UPDATE a SET status = 0 WHERE ISNULL(status, 0) <> 1
不好意思,现在表a中没有CompanyName字段,打错了。抱歉?
update a set a.Status = case when b.ProductsName is null then 0 else 1 end from a inner join c on a.customerId=c.customerId left join b on a.ProductsName=b.ProductsName and a.Trade=b.Trade and a.ProductsModel=b.ProductsModel and c.CompanyName=b.CompanyName
update a set Status = (case when exists (select 1 from (select b.ProductsName,b.Trade,b.ProductsModel,c.CompanyName from b,c where b.customerId = c.customerId) m where m.ProductsName=n.ProductsName and m.Trade=n.Trade and m.ProductsModel=n.ProductsModel and m.CompanyName=n.CompanyName) then 1 else 0 end) from a n
inner join c on b.customerId=c.customerId
right join a on a.ProductsName=b.ProductsName and a.Trade=b.Trade and a.ProductsModel=b.ProductsModel and a.CompanyName=c.CompanyName
SET status = 1
WHERE EXISTS ( SELECT 1
FROM ( SELECT *
FROM b
INNER JOIN c ON b.customerid = c.customerid
) AS t
WHERE a.productsname = t.productsname
AND a.trade = t.trade
AND a.productsmodel = t.productsmodel
AND a.companyname = t.companyname )
UPDATE a
SET status = 0
WHERE ISNULL(status, 0) <> 1
inner join c on a.customerId=c.customerId
left join b on a.ProductsName=b.ProductsName and a.Trade=b.Trade and a.ProductsModel=b.ProductsModel and c.CompanyName=b.CompanyName
(case when exists (select 1 from (select b.ProductsName,b.Trade,b.ProductsModel,c.CompanyName from b,c where b.customerId = c.customerId) m
where m.ProductsName=n.ProductsName and m.Trade=n.Trade and m.ProductsModel=n.ProductsModel and m.CompanyName=n.CompanyName) then 1 else 0 end)
from a n