我有一个问题:我试图在SQL Server 2012中完全更新临时表(在存储过程中),但它只更新与我的描述匹配的第一个条目。下面是代码:
create table #t (store_name varchar(30),
product_name varchar(30),
price int,
valab_since date,
valab_until date,
best_offer varchar(3))
--some code that populates my table
update #t set best_offer = 'yes'
where price = (select min(price) from Cataloage as c
INNER JOIN Produse as p
on c.codP = p.codP
where p.denumire = #t.store_name)
update #t set best_offer = 'no'
where price > (select min(price) from Cataloage as c
INNER JOIN Produse as p
on c.codP = p.codP
where p.denumire = #t.product_name)
select * from #t
Cataloage
和Produse
是我使用的一些表
要覆盖所有记录,首先将它们全部设置为"no",然后运行单独的查询,仅更新具有商店最小价格的记录。
update #t set best_offer = 'no';
;with t as (
select *, rnk = dense_rank() over (partition by t.store_name order by price asc)
from Cataloage c
join Produse p on c.codP = p.codP
join #t t on p.denumire = t.store_name
)
update t
set best_offer = 'yes'
where rnk = 1;
我使用了DENSE_RANK和一个公共表表达式作为逐行子查询的替代方法。