Not sure why you want to do this, but it's not terribly difficult. Here's the code:
create table #test (
item varchar(100),
[status] varchar(10),
qty int
);
insert into #test (item, [status], qty)
select 'Apple','IN',2
union all
select 'Apple','OUT',90
union all
select 'Apple','OUT',20
union all
select 'Orange','OUT',10
union all
select 'Orange','IN',200
union all
select 'Banana','IN',10;SELECT *
FROM #test
ORDER BY item, [status], qty;
UPDATE T
SET T.qty = T2.qty
FROM #test AS T
INNER JOIN #test AS T2
ON T.item = T2.item
AND T2.[status] = 'IN'
WHERE T.[status] = 'OUT';
SELECT *
FROM #test
ORDER BY item, [status], qty;
DROP TABLE #test;
Do you understand how that worked?
EDIT: just realized that your selected data doesn't match the supplied inserted data. Not sure if that matters as it seems like a copy and paste problem where fixing all the values to match just didn't quite take place.
Thanks sgmunson. Yes I did understand.
Any other way we can write the same query? Can we qrite a co-related sub-qeury? which one is better performance wise?
There are a number of ways to write the same query, but unless you do something truly strange, there's a pretty good chance you end up with the exact same execution plan. You could have used a CTE, or a subquery, but either one would have to be either JOINed to or be the object of a CROSS APPLY. As to performance, I'm doubtful that in this case that there might be much of a difference, but without having your environment in which to test, there's no way to know. Please feel free to test and post back with your observations. As to your specific suggestion of a correlated sub-query, I probably wouldn't use that term for any of the ways to re-write this exact same functionality.