a better qeury than this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

a better qeury than this?

Author

Topic

crichardson
Starting Member

6 Posts

Posted - 2012-05-08 : 09:05:17

The example query below sums a stock value between two 'stores' making the stock value the same for both stores. I just wondered if anyone could come up with a neater and/or more efficient query? Perhaps without a sub-query?

Thanks,
Clive

create table #tbl
(
ID smallint,
article smallint,
store smallint,
stock smallint
)

insert into #tbl values (1,1001,123,0)
insert into #tbl values (2,1002,123,2)
insert into #tbl values (3,1003,123,0)
insert into #tbl values (4,1004,123,5)

insert into #tbl values (5,1001,456,1)
insert into #tbl values (6,1002,456,3)
insert into #tbl values (7,1003,456,2)
insert into #tbl values (8,1004,456,1)
insert into #tbl values (9,1005,456,3)

insert into #tbl values (10,1001,998,0)
insert into #tbl values (11,1002,998,20)
insert into #tbl values (12,1003,998,10)
insert into #tbl values (13,1004,998,12)
insert into #tbl values (14,1005,998,0)

insert into #tbl values (15,1001,999,0)
insert into #tbl values (16,1002,999,1)
insert into #tbl values (17,1003,999,3)

-- Is there a better query than the following to achieve the same result?

UPDATE #tbl
SET #tbl.stock = a.stock
FROM #tbl
inner join
(
SELECT
--store,
article,
sum(stock) AS stock
FROM
#tbl
WHERE
store in ('998', '999')
GROUP BY
article
) a ON a.article = #tbl.article
WHERE #tbl.store in ('998', '999')

select * from #tbl

drop table #tbl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-08 : 09:15:17

[code]
update t
set stock = sum_stock
from
(
select *, sum_stock = sum(stock) over(partition by article)
from #tbl t
where store in ('998', '999')
) t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-08 : 09:20:12

Try this

UPDATE	#Tbl
SET	Stock =	(
			SELECT		SUM(b.Stock) AS Stock
			FROM		#Tbl AS b
			WHERE		b.Store BETWEEN 998 AND 999
					AND b.Article = #Tbl.Article
		)
WHERE	Store BETWEEN 998 AND 999

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources