1

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
Improve this question
8
  • What error message are you getting?
    – Neil N
    Commented Jul 5, 2011 at 14:59
  • 1
    Which DBMS? And isn't exactly working is not an error message I have ever seen in any DBMS I worked with. What exactly isn't working?
    – user330315
    Commented Jul 5, 2011 at 14:59
  • So if I am getting this... you have a sum column that will be on every row? That will store the sum from the same table? Perhaps your table design needs some refactoring?
    – Neil N
    Commented Jul 5, 2011 at 15:00
  • 1
    The current code wouldn't change anything, the SET is updating a value that based on the JOIN would already equal that value. I could easily fix your syntax to make it work, but the code would have no effect. We need to know what you're actually trying to update and on what condition, it currently makes little sense.
    – Fosco
    Commented Jul 5, 2011 at 15:01
  • 1
    I cant understand what exactly you want to achieve from it. You are updating the column SumAssessments with the SUM (Assessment.Amount) where as your are applying equal inner join on same column, can understand why you want to update this column if there are already equal?
    – Deepesh
    Commented Jul 5, 2011 at 15:09

2 Answers 2

Reset to default
5

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
Improve this answer
1
  • You are correct, thanks for the help! I'm very new to SQL, so these concepts are very new to me.
    – exogenic
    Commented Jul 5, 2011 at 15:22
1

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
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.