How to use exists on this sql statment ?

Question

How to use exists on this sql statment ? 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)

Kiran Mohanty · Answer

Can you please share all 3 table data just by running "select * from " and share the output you wish to see? I have a strong feeling you are looking for sub query .

ahmed elbarbary · Answer

select zpartid,rohsstatus from parts.rohs select partid from #manufacture select acceptedvaluesoptionid,Name from nop_acceptedvaluesoption acceptedvaluesoptionid equal rohsstatus

ahmed elbarbary · Answer

i try that but 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 ) Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "RHst.Name" could not be bound.

ahmed elbarbary · Answer

and this statement is remaining i need it so i add it inside exist or outside can you please show me inner JOIN Nop_AcceptedValuesOption RHst WITH (NOLOCK) on RHst.AcceptedValuesOptionID=Rhh.RoHSStatus AND RHst.AcceptedValuesID=911

Kiran Mohanty · Answer

Please try as below. update m set m.rohsstatus=RHst.Name from #ManuFacture m where exists( select Rhh.ZPartID from Parts.ROHS s where s.Rhh.ZPartID = m.PartID ) https://www.w3schools.com/sql/sql_exists.asp