CODEWITHSUNDEEP
sql
exogenic
exogenic

Reputation: 69

Using SQL Join and Update together

I'm attempting to update a table using a join statement, but it isn't exactly working out. I need to be able to update a certain row in the database when one condition is true, using data from a different table. Here is what I have so far, maybe I am close and someone could provide some insight?

UPDATE Project
   SET Project.SumAssessments = SUM (Assessment.Amount)
 FROM Project
 JOIN Assessment ON Project.SumAssessments = SUM (Assessment.Amount)
WHERE Assessment.ProjectCode = @ProjectID
  AND Project.ProjectID = @ProjectID

Upvotes: 1

Views: 297

Answers (2)

Fosco
Fosco

Reputation: 38526

I have to assume you're trying to update the SumAssessments column, not query based off of it, so the SUM function should not appear in your join clause. You don't need a join at all, and despite these 'sum' columns being a bad idea, here's how you can accomplish what you wanted:

UPDATE Project set SumAssessments = (
    select SUM(Amount) from Assessments where ProjectCode = @ProjectID
) 
where ProjectID = @ProjectID

Upvotes: 1

Tom H
Tom H

Reputation: 47392

First, I would get rid of the column entirely. It violates one of the most basic rules of proper database design. Don't duplicate data in your database, because it will pretty much always end up out of sync and even when it doesn't it will require extra maintenance and care. There are VERY rare cases where this should be violated. Your situation is almost certainly not one of those cases, even if you think it is. With proper indexing there should be no need for that column.

Second, there's no need to join on the Assessment table based on the SUM. You're already joining based on the project code, you just didn't put that condition in your ON clause where it belongs. Even better, you could just use a subquery to avoid the need for a GROUP BY. Since you're using what I assume is the Primary Key (ProjectId) the subquery should perform fine.

UPDATE Project
SET Project.SumAssessments = (
        SELECT SUM (Assessment.Amount)
        FROM Assessment
        WHERE ProjectCode = @ProjectID
        )
 FROM Project
WHERE Project.ProjectID = @ProjectID

Upvotes: 5

Related Questions