3

I'm very new to Sql, and having trouble wrapping my brain around the syntax. I have a table PM (with Fields Acct, Amt, Action, Reason), and I need to update a few fields in the table wherever count(Acct)>1, and the Amt is the same. Specifically, based on this select statement:

SELECT PM.Acct, PM.Amt
FROM PM
GROUP BY PM.Amt
HAVING (((Count(PM.Acct))>1));

That code returns the records I know need to be changed, anyway. Once someone explains it, I'm sure it will be obvious, but I'm stuck.

graidan
  • 151
  • 2
  • 11

1 Answers1

2

You can do it this way, then you are still using your GROUP BY for your Amt. This was tested in MS Access 2003 and only updates the records in the sub-query:

UPDATE PM
SET PM.Amt = newValue
    , (other fields and values)
WHERE EXISTS
(
    SELECT t.Acct, t.Amt
    FROM PM t
    WHERE PM.Acct = t.Acct
    GROUP BY t.Acct, t.Amt
    HAVING Count(t.Acct)>1
)
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • I get what you're doing, and that helps a ton, but I get an error: You tried to execute a query that does not include the specified expression 'Acct' as part of an aggregate function. – graidan Mar 27 '12 at 15:27
  • You can add to the subquery GROUP BY, t.Acct. I edited my answer – Taryn Mar 27 '12 at 15:31
  • I'm still getting an error, but it's a data type mismatch, and that's all on me. Thank you! – graidan Mar 27 '12 at 15:36
  • if this solution works for you, be sure to mark it as the accepted answer via the checkmark on the left. – Taryn Mar 27 '12 at 15:39