CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user1435281
user1435281

Reputation: 123

TSQL Update using MAX()

I'm trying to update a column to a max value, but only when grouped by the appropriate identifier.

SQL Fiddle for what I'm trying to do with trial & failed code :(.

SQL Fiddle for what I want it to look like after update.

Any assistance you could lend me will be greatly appreciated. As I understand it max() just takes the largest number of the column, so how do I sort in an update statement without wrecking it?

Upvotes: 3

Views: 6292

Answers (1)

anon
anon

Reputation:

Why do you want to store this redundant data in a separate column, when you can always calculate it at run time? You know that you will have to keep this up to date with triggers or some other mechanism any time any single row in the table is updated, right?

UPDATE h 
  SET LAST_REVISION = s.lr
  FROM dbo.PO_HEADER AS h
  INNER JOIN 
  (
    SELECT PO_NUMBER, lr = MAX(REVISION_NUMBER)
    FROM dbo.PO_HEADER
    GROUP BY PO_NUMBER
  ) AS s
  ON h.PO_NUMBER = s.PO_NUMBER;

Upvotes: 9

Related Questions