CODEWITHSUNDEEP
sqlsql-server
JustSomeNewbie
JustSomeNewbie

Reputation: 115

Update many(multiple) rows

I'm having some problem with update. When I got one record in the database, then it works correctly, but when I got more than 1, it didn't work correctly.

Here is code :

UPDATE rozl 
SET amountAfter = (SELECT amountBefore - (amount + destroyed) FROM rozl)

Table looks like

  id    number   amountBefore    amount    destroyed    amountAfter
   1         3            100        30           20             50
   2         4            50         10           3             NULL

When I'm running the update, I expect amountAfter to get fill with value.

The error which I'm getting :

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

Then I run this Select from update, I get 2 rows which are correct but I can't update it.

Output from select is :

   1   50
   2   37

As I said, it's correct but i had no idea how to write it in update.

Upvotes: 1

Views: 51

Answers (1)

sgeddes
sgeddes

Reputation: 62861

You don't need the subquery:

update rozl
set amountAfter= amountBefore - (amount + destroyed)

As commented, you do need the parentheses as the order of operations matter.

Upvotes: 5

Related Questions