CODEWITHSUNDEEP
sqlsql-server
Julez
Julez

Reputation: 65

Insert avarage value from Table one into Table2

I try to accomplish something like this:

UPDATE Users
SET totalPayed = AVG(p.paymentMade)
FROM Payments p
WHERE Users.username = p.username;

Error message:

An aggregate may not appear in the set list of an UPDATE statement.

How can I solve this without using AVG in the SET line?

Upvotes: 0

Views: 25

Answers (2)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

You can achieve it by doing an avg in subquery and joining it to the main table using username and then update the totalpayed.

UPDATE U
  SET totalPayed=p.avg_payment
FROM Users U
INNER JOIN 
( SELECT username,AVG(p.paymentMade) avg_payment
  FROM Payments 
  GROUP BY username ) P ON U.username =p.username

Upvotes: 1

Arion
Arion

Reputation: 31239

I think you can solve this be using a sub query. Like this:

UPDATE Users
SET totalPayed = 
(
    SELECT AVG(p.paymentMade) 
    FROM Payments p
    WHERE Users.username = p.username;
)

The thing is that if you are using a JOIN then for the users that do not have payment you will not set the value

Upvotes: 0

Related Questions