Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Compare Two Rows Same ID and Update?

Posted on 2014-01-27
Medium Priority
360 Views
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
Question by:WorknHardr
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 70

Accepted Solution

Scott Pletcher earned 800 total points
ID: 39813372
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

LVL 16

Assisted Solution

Surendra Nath earned 800 total points
ID: 39813493
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
0

Author Closing Comment

ID: 39813511
Very nice, thx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll