UPDATE and GROUP BY Query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE and GROUP BY Query?

Author

Topic

triplee23
Starting Member

4 Posts

Posted - 2012-04-22 : 14:40:01

Hi,

I am trying to construct an UPDATE query based on a JOIN between two tables and where the second table is a GROUP BY.

The tables:

tblCustomers (simplyfied)

CustomerID MaxReading MaxReadingDate
-------------------------------------

tblReadings (simplyfied)

ReadingID CustomerID Reading ReadingDate
----------------------------------------

The table have a one-to-many relation, tblCustomers -> tblReadings

What I want to do is:

Through an UPDATE on tblCustomers (for every customer) find the MAX(Reading) in tblReadings (and the corresponding ReadingDate) and update the tblCustomers.MaxReading and tblCustomers.MaxReadingDate with the related Max(tblReadings.Reading) and tblReadings.ReadingDate.

I can't figure this out as we are talking about UPDATE, GROUP BY MAX(in the tblReadings) WHERE tblCustomers.CustomerID = tblReading.CustomerID (or based on some kind of subquery).

I hope I have been able to explain the issue.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-22 : 15:04:01

You can use a CTE or subquery to get the rows with the highest readings and use that to update the Customers table - for example like this:

;WITH A AS 
(
	SELECT
		CustomerId,
		Reading,
		ReadingDate,
		ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY Reading DESC) AS RN
	FROM
		tblReadings
)
UPDATE c SET
	MaxReading = a.Reading,
	maxReadingDate = a.ReadingDate
FROM
	tblCustomers c
	INNER JOIN A ON a.CustomerId = c.CustomerId
WHERE
	RN = 1;

If you have multiple ReadingDates with the same Reading, which happens to be the maximum reading, what ReadingDate do you want to put into the Customers table?

triplee23
Starting Member

4 Posts

Posted - 2012-04-22 : 15:20:35

Hi,

Can I accomplish this without the WITH statement, as I am also adding this to an Access 2010 user interface? Access 2010 cannot handle WITH. Also, I am not sure I understand your last question?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-22 : 15:42:47

I am familiar with Access only in passing, so the query below is only an educated guess.

UPDATE tblCustomers SET
	MaxReading = 
		(SELECT TOP 1 Reading FROM tblREadings 
		 WHERE tblCustomers.CustomerId = tblREadings.CustomerId
		 ORDER BY Reading DESC),
	MaxReadingDate =
		(SELECT TOP 1 ReadingDate FROM tblReadings 
		 WHERE tblCustomers.CustomerId = tblREadings.CustomerId
		 ORDER BY Reading DESC)

Regarding my question in my previous post, I will illustrate with an example. Suppose for customer 100, these are the readings:


CustomerId   Reading   ReadingDate
100           11.7       12/26/2011
100            0.2       12/27/2011
100           11.7       12/28/2011

In this example, what do you want to see in the Customers table? Your choices of course are 11.7 and 12/26/2011 OR 11.7 and 12/28/2011.

triplee23
Starting Member

4 Posts

Posted - 2012-04-22 : 15:52:56

Hi,

I gave this a try, unfortunately I got an error that this was not an updateable query, something I have experienced many times. Maybe this is not possible. I don't know how to make is updateable.

Regarding you question, the reason I am creating this query is to secure that a new reading entry for a given customer is ALWAYS higher that the previous, so my answer would be that this could not happen based on the valition during entry.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-22 : 16:12:44

I am sorry I couldn't be of more help. You might want to repost the question to the Access forum on SQL Team, or even better, to another forum that specializes in Access. Most people on SQL Team are oriented towards SQL Server.

My last attempt:

UPDATE c SET
	maxReadingDate = s.MaxReading,
	maxReadingDate = r.ReadingDate
FROM
	tblCustomers c
	INNER JOIN tblReadings r ON r.CustomerId = c.CustomerId
	INNER JOIN
	(
		SELECT
			CustomerId,
			MAX(Reading) AS MaxReading
		FROM
			tblReadings
		GROUP BY
			CustomerId
	) s ON s.CustomerId = r.CustomerId
		AND s.MaxReading = r.Reading

triplee23
Starting Member

4 Posts

Posted - 2012-04-22 : 16:32:57

I thank you for you help. Could you have a look at it one last time. I get some sytax error and can't figure out why. I really appreciate the time you have spent on this.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-22 : 19:28:51

What is the error message? Is it in Access or SQL? If it is Access, I dont have Access installed to test it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources