June 18, 2018 at 8:00 am
Hi Experts,
Need help on an update query.
I want to take all status = 'IN' records qty values and update those value as "qty" value for 'OUT' records for that particular item.
create table test
(item varchar(100),
status varchar(10),
qty int
)
insert into test
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;
item status qty
Apple IN 2
Apple OUT 90
Apple OUT 20
Orange OUT 10
Orange IN 200
Banana IN 10
What are the different ways of writing TSQL query to get below output?
Expected output
===========
item status qty
===================
Apple IN 2
Apple OUT 2
Apple OUT 2
Orange OUT 200
Orange IN 200
Banana IN 10
Thanks,
Sam
June 18, 2018 at 8:16 am
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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2018 at 8:16 am
vsamantha35 - Monday, June 18, 2018 8:00 AMHi Experts,
Need help on an update query.
I want to take all status = 'IN' records qty values and update those value as "qty" value for 'OUT' records for that particular item.
create table test
(item varchar(100),
status varchar(10),
qty int
)insert into test
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',10select * from test;
item status qty
Apple IN 2
Apple IN 90
Apple IN 20
Orange IN 10
Orange IN 200
Banana IN 10I tried below join, but was throwing different error in onprem db and different on a PaaS db.
What are the different ways of writing TSQL query to get below output?
Expected output
===========
item status qty
===================
Apple IN 2
Apple OUT 2
Apple OUT 2
Orange OUT 200
Orange IN 200
Banana IN 10Thanks,
Sam
Can you please clarify this a bit further, the data does neither match the description nor the results posted.
😎
June 18, 2018 at 8:30 am
This request just doesn't seem right. I don't know why anyone would actually need to do this but my initial impression is that it's on the edge of "data tampering".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2018 at 8:56 am
Extremely sorry, my bad. there were some typos. Updated my original post.
June 18, 2018 at 8:59 am
sgmunson - Monday, June 18, 2018 8:16 AMNot 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.
June 18, 2018 at 9:52 am
vsamantha35 - Monday, June 18, 2018 8:59 AMsgmunson - Monday, June 18, 2018 8:16 AMNot 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2018 at 10:31 am
Thanks Steve for the suggestions.
June 18, 2018 at 10:39 am
vsamantha35 - Monday, June 18, 2018 10:31 AMThanks Steve for the suggestions.
You're very welcome.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply