CODEWITHSUNDEEP
sql-servert-sql
user3934469
user3934469

Reputation: 23

update statement with select and case in SQL Server

I'm facing a problem when I try to execute update statement In SQL Server 2012:

update ScmInOutD 
    set QtyValue= (select case when ScmInOutMas.InOutType ='I' or  ScmInOutMas.InOutType ='B' then Qty else Qty*-1 end  Qty
from 
    ScmInOutMas,ScmInOutD where ScmInOutD.InOutID = ScmInOutMas.InOutID)

Error Msg Show To me:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 1

Views: 67

Answers (1)

Viki888
Viki888

Reputation: 2774

Try using JOIN in UPDATE statement

update SD
set SD.QtyValue= (case when  SM.InOutType ='I'  or  SM.InOutType ='B'  then Qty else Qty*-1  end)
FROM ScmInOutD SD
JOIN ScmInOutMas SM ON SD.InOutID = SM.InOutID

Upvotes: 1

Related Questions