Solved

SQL Compare Two Rows Same ID and Update?

Posted on 2014-01-27
3
350 Views
Last Modified: 2014-01-27
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
Comment
Question by:WorknHardr
3 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 200 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

by:Surendra Nath
Surendra Nath earned 200 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

Open in new window

0
 

Author Closing Comment

by:WorknHardr
ID: 39813511
Very nice, thx
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now