CODEWITHSUNDEEP
sqlsql-serversql-update
Radwa
Radwa

Reputation: 65

Update statement based on another table

I am trying to update column in table get it from another table and I have relation between two tables as parent have primary key "tbl_Inv_ClientItemsReturnOrders" and child have foreign key "tbl_Inv_ClientItemsReturnOrderDetails".

I try this 

update U
set U.InventoryReturnReasonID =
        (select InventoryReturnReasonID
         from tbl_Inv_ClientItemsReturnOrders
         where ClientItemsReturnOrderID = U.ClientItemsReturnOrderID)
from [dbo].[tbl_Inv_ClientItemsReturnOrderDetails] U

But I get this error

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

I want to update column "InventoryReturnReasonID" in table "tbl_Inv_ClientItemsReturnOrderDetails" by value of column "InventoryReturnReasonID" from table "tbl_Inv_ClientItemsReturnOrders"

Upvotes: 0

Views: 91

Answers (2)

Midhun m k
Midhun m k

Reputation: 66

you can use the query given below for getting better idea about the issue.

 SELECT * FROM [dbo].[tbl_Inv_ClientItemsReturnOrderDetails] U
 INNER JOIN [dbo].[tbl_Inv_ClientItemsReturnOrders] P ON (P.ClientItemsReturnOrderID = U.ClientItemsReturnOrderID)

we have the option to compile update with join. The query is as follows

 UPDATE U
 SET U.InventoryReturnReasonID = P.InventoryReturnReasonID
 FROM [dbo].[tbl_Inv_ClientItemsReturnOrderDetails] U
 INNER JOIN [dbo].[tbl_Inv_ClientItemsReturnOrders] P ON (P.ClientItemsReturnOrderID = U.ClientItemsReturnOrderID)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The error is pretty clear. You can select an arbitrary value using TOP 1:

Update U
  set U.InventoryReturnReasonID = (select TOP 1 ro.InventoryReturnReasonID
                                   from tbl_Inv_ClientItemsReturnOrders ro
                                   where ro.ClientItemsReturnOrderID = U.ClientItemsReturnOrderID
                                  )
  from [dbo].tbl_Inv_ClientItemsReturnOrderDetails U;

Normally, TOP should have an ORDRER BY. You can include an ORDER BY to specify which of multiple values the update should use.

Note: Using qualified column names (i.e. including a table alias) is always a good idea. It should be mandatory with a correlated subquery because of the potential danger if something goes wrong. For instance, your query would return too many rows if the column ro.ClientItemsReturnOrderID did not exist.

Another approach is to use aggregation:

Update U
  set U.InventoryReturnReasonID = (select max(ro.InventoryReturnReasonID)
                                   from tbl_Inv_ClientItemsReturnOrders ro
                                   where ro.ClientItemsReturnOrderID = U.ClientItemsReturnOrderID
                                  )
  from [dbo].tbl_Inv_ClientItemsReturnOrderDetails U;

Upvotes: 0

Related Questions