declare @products table(
productuid int,
labeluid int
)

insert into @products values (46047,-1) 
insert into @products values (46047,-1) 

declare @labels table(
productuid int,
labeluid int,
ctype varchar(10),
version int
)

insert into @labels values (46047,123,'landscape',1)
insert into @labels values (46047,345,'portrait',2)

update ct
set ct.labeluid = cv.labeluid
from @products ct
	join @labels cv on ct.productuid = cv.productuid
where cv.version = (
	select max(version) from @labels cv2
	where cv.productuid = cv2.productuid
	and cv.ctype = cv2.ctype
)

select * from @products

productuid  labeluid    
----------- ----------- 
46047       345
46047       345

(2 row(s) affected)

quote:

---

Originally posted by jonsey54

In the update statement I am doing a join with the labels table based on the productuid. In the same statement I am also specifying the latest version of the label in the where clause. So I would've expected the output

46047 345
46047 345

the 345 coming from the join with the labels table and taking the max version of the label.

Many Thanks

---

quote:

---

Originally posted by jonsey54

Thanks for the replies.

Harsh, I am still getting 123 returned as the label in my result sets. I don't understand how you are getting 345.

I still don't understand why I'm getting 123.

visakh16, could you explain a little more what you would expect to see as the output and why it would be like that. I understand what you mean by it would return the max version for each type but then I thought ut would return 4 results instead of 2, e.g.,

productuid labeluid
----------- -----------
46047 123
46047 345
46047 123
46047 345

Many Thanks

---