• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

SQL Compare Two Rows Same ID and Update?

I'm at-a-loss trying to compare two table rows with the same ID.
I need to update the 'IsPriceChg' in the row where Price column data is greater.

I tried using  Select 'Top 1' ...

ID      [Date]                                           IsPriceChg      Harmonica
88      2012-07-29 00:00:00.000      NULL                1.00
88      2012-07-30 00:00:00.000      NULL                2.00

I have this much, but not working yet:


Update Toys
Set IsPriceChg = 1
where ListID = 88
and max([Harmonica]) > min([Harmonica])
0
WorknHardr
Asked:
WorknHardr
2 Solutions
 
Scott PletcherSenior DBACommented:
Update t
Set IsPriceChg = 1
FROM Toys t
INNER JOIN (
    SELECT ListID, MAX([Harmonica]) AS Max_Harmonica
    FROM Toys
    GROUP BY ListID
    HAVING MAX([Harmonica]) > MIN([Harmonica])
) AS t_max ON
    t_max.ListID = t.ListID AND
    t.Harmonica = t_max.Harmonica
0
 
Surendra NathTechnology LeadCommented:
if you are using SQL 2005 or above you can use the below query

;with CTE  AS
(
 select row_number() OVER(partition by ID,ListID order by Harmonica desc) rn, * from Toys
)
UPDATE CTE
SET IsPriceChg = 1 
WHERE Rn= 1

Open in new window

0
 
WorknHardrAuthor Commented:
Very nice, thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now