How to use exists on this sql statment ?

Question

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)

Answer 1

Hi @ahmed salah ,

Agreed with Tom,you could not do an UPDATE statement together with WHERE EXISTS table if you would like to update table ManuFacture where part id exist on Parts.ROHSsince since there is no data returns.

The best way you could refer is using INNER JOIN.

But you could use update ..WHERE EXISTS in below situations:

One: update the column with another column (for example, 'name') in the same table.

update m set m.rohsstatus=m.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 )  

Two: update the column with a fixed value(for example, 'Pass').

update m set m.rohsstatus='Pass'    
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 )   

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Next time please post table design as DDL, some sample data as DML and the expected result

This here?

update m 
set m.rohsstatus=RHst.Name 
from #ManuFacture m
where exists( **select 1 from Parts.ROHS AS Rhh WHERE Rhh.ZPartID=m.PartI** )

Answer 3

Sorry, from this point on your post is only confusing.

Again, please post table design as DDL, some sample data as DML and the expected result, without it's not really possible to assist you.