updating a column based on a min(value) in the where clause

Question

Post reply

updating a column based on a min(value) in the where clause

cs_source

Default port

Points: 1459
More actions
October 8, 2013 at 3:48 pm

#284417

I would like to update a column in a table from a field in another table but based on the minimum value of a column, example
table: carinfo
id, car, color, rank
1, vw, grey, 1
1, vw, white, 2
1, vw, red, 3
1, vw, blue,4
2, audi, black, 1
2, audi, white, 2
into a table so the query i have is as follows:
update table cars set color = color from carinfo where cars.id = carinfo.id and [min (rank)?]
i can't seem to figure it out, any help is appreciated
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

cs_source Default port Points: 1459 More actions · Answer 1

it seems i have posted to soon, the solution for this problem is

update table cars set color = color from carinfo where cars.id = carinfo.id and rank = (select min(rank) from carinfo where car.id=carinfo.id)

Now i just need to figure out how to update another record based on previous rank+1

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

If you want some really good help, try providing some sample data that we can use and required output.

People will soon jump in to help.

How to post code problems

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

cs_source Default port Points: 1459 More actions · Answer 3

thank you i will post more details in my future posts 🙂

Hunchback SSCrazy Points: 2649 More actions · Answer 4

Try:

With C1 as (

Select id, color, row_number() over(partition by id order by rank) as rn

From carinfo

)

Update A

Set A.color = C1.color

From cars as A inner join C1 on A.id = C1.id and C1.rn = 1;

GO

If the minimum rank per [id] is always one then you do not need the CTE.