0

I have some data, and wherever the server_id, product_id, and polled_at date match, i want to set updated_by to 2 (only searching rows where updated by is already 1). I have been messing with my code for a while, but can't figure it out. Please help me. here is what I have.

http://sqlfiddle.com/#!2/ebd112/1

The first 2 rows should not get updated, and only the max peak between all other rows where the server_id, product_id, and polled_at match up, should be updated to updated_by=2

Thanks

Candice
  • 25
  • 6

1 Answers1

0

You will need to add a HAVING clause to your query. Here is the updated fiddle: http://sqlfiddle.com/#!2/9e3a9/1

Basically, rather than trying to sort it as you were initially doing, I changed the ORDER BY into a HAVING clause:

HAVING licenses_peak = (SELECT Max(licenses_peak) FROM hpolls WHERE updated_by=1)

This will cause the query to only include those items that have the maximum licenses_peak value for that grouping.

dub stylee
  • 3,252
  • 5
  • 38
  • 59
  • that is not what I need. I want to update all records (except where updated_by=0), and only set updated_by to 2 if the duplicate where server_id, and prouduct_id, and polled_at has a greater peak. – Candice Feb 24 '14 at 21:30
  • I guess I am not understanding your question. That fiddle that I posted does exactly that.. which items in your list should it be updating? – dub stylee Feb 24 '14 at 22:10
  • I got it working using this: UPDATE hpolls set updated_by=2 where id in ( SELECT id FROM (select * from hpolls where updated_by=1 order by licenses_peak desc) as sub GROUP BY server_id, product_id, polled_at); – Candice Feb 24 '14 at 22:41
  • That doesn't seem like it would work, unless you made it `SELECT TOP 1 id FROM ...` otherwise it will still be updating all of your records that have `updated_by=1`. Just ordering by licenses_peak descending doesn't do anything by itself, you have to specify how many records you want the query to return, in this case the subquery. – dub stylee Feb 24 '14 at 23:09