How to use exists on this sql statment?

Transact-SQL
,
1

How to use Exists on this sql statement ?

update m set m.rohsstatus=RHst.Name from #ManuFacture m

                         inner JOIN Parts.ROHS Rhh WITH(NOLOCK) ON Rhh.ZPartID=m.PartID
             inner JOIN Nop_AcceptedValuesOption RHst WITH(NOLOCK) on RHst.AcceptedValuesOptionID=Rhh.RoHSStatus AND RHst.AcceptedValuesID=911

i need to update table ManuFacture where part id exist on Parts.ROHS

so i need it as below

update m set m.rohsstatus=RHst.Name from #ManuFacture m
where exists(select 1 from Parts.ROHS)

so i need to replace statement above by using exists but i can't do

2

What makes you think you want to use EXISTS?

RHist.[Name] will not be available from an EXISTS check.

3

so please can you write to me correct statment

4

wrong statement give me error

	update m set m.rohsstatus=RHst.Name	   from #ManuFacture m
						where exists(select 1 from

							 Parts.ROHS Rhh WITH(NOLOCK) 
				inner JOIN Nop_AcceptedValuesOption RHst WITH(NOLOCK) 
				on RHst.AcceptedValuesOptionID=Rhh.RoHSStatus AND RHst.AcceptedValuesID=911
				where m.partid=Rhh.zpartid )
5

You could try an ANSI style UPDATE but it does not look very efficient:

UPDATE #ManuFacture
SET rohsstatus = 
	(
		SELECT TOP (1) RHst.[Name]
		FROM Nop_AcceptedValuesOption RHst
		WHERE RHst.AcceptedValuesID=911
		AND EXISTS
		(
			SELECT 1
			FROM Parts.ROHS Rhh
			WHERE Rhh.RoHSStatus = RHst.AcceptedValuesOptionID
				AND Rhh.ZPartID = #ManuFacture.PartID
		)
		ORDER BY RHst.[Name]
	)
WHERE EXISTS
(
	SELECT 1
	FROM Nop_AcceptedValuesOption RHst
	WHERE RHst.AcceptedValuesID=911
	AND EXISTS
	(
		SELECT 1
		FROM Parts.ROHS Rhh
		WHERE Rhh.RoHSStatus = RHst.AcceptedValuesOptionID
			AND Rhh.ZPartID = #ManuFacture.PartID
	)
);

Using MERGE should also give you portable code even though it is not well implemented in SQL SERVER.

Personally I would go with a tsql JOIN:

WITH NewVals
AS
(
	SELECT Rhh.ZPartID, RHst.[Name]
		,ROW_NUMBER() OVER (PARTITION BY Rhh.ZPartID ORDER BY RHst.[Name]) AS rn
	FROM Nop_AcceptedValuesOption RHst
		JOIN Parts.ROHS Rhh
			ON RHst.AcceptedValuesOptionID = Rhh.RoHSStatus
	WHERE RHst.AcceptedValuesID=911
)
UPDATE M
SET rohsstatus = N.[Name]
FROM #ManuFacture M
	JOIN NewVals N
		ON M.PartID = N.ZPartID
			AND N.rn = 1
WHERE M.rohsstatus <> N.[Name];