How to use exists on this sql statment ?

ahmed elbarbary

5y795

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)

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary5y ago
select zpartid,rohsstatus from parts.rohs
select partid from #manufacture
select acceptedvaluesoptionid,Name from nop_acceptedvaluesoption

acceptedvaluesoptionid equal rohsstatus
Kiran Mohanty5y ago
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 elbarbary5y ago
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 elbarbary5y ago
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 Mohanty5y ago
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