更新前n个列，其中n来自另一个表 [英] Update top n number of columns where n comes from another table

问题描述

我有一张桌子可以在另一张桌子上给出要更新的项目数量，这两张桌子都有一个共同的栏目

例如



＃table1



类型

目的

Itemstoupdate



＃table2

名称

类型

用途

标志



我需要在＃table2中将标志更新为1

表示那些与table1中的Itemstoupdate相等的记录，其中类型和用途匹配



逻辑就像下面那样...但是语法我不知道。请帮帮我



我的尝试：

I have one table which is giving count of items to be updated in another table both having one column in common
for eg

#table1

Type
Purpose
Itemstoupdate

#table2
Name
Type
Purpose
Flag

I need to update flag to 1 in #table2
for those no of records which is equal to Itemstoupdate from table1 where type and purpose match

logic is something like the below one ..but the syntax i dont know. Please help me

What I have tried:

update top (Itemstoupdate)#table2
set Flag=1
from #table1 
where #table1.type=#table2.type and #table1.Purpose=#table2.purpose

推荐答案

不幸的是你的逻辑不起作用 - 你有Itemstoupdate你需要一个常量值。你可以使用Common Table Expression（CTE）实现它



例如

Unfortunately your logic won't work - where you have Itemstoupdate you need a constant value. You can achieve it using a Common Table Expression (CTE)

e.g.

;with CTE AS
(
	SELECT ROW_NUMBER() OVER (ORDER BY NAME) AS RN,
	name, type, purpose, flag
	FROM #table2
)
Update A SET FLAG = 1
from CTE A
INNER JOIN #table1 B on  A.type = B.type and A.purpose = B.purpose
WHERE A.RN <= B.itemstoupdate

关键点h ere是 ROW_NUMBER 函数的 ORDER BY 子句 - TOP 没有 ORDER BY

The key point here is the ORDER BY clause on the ROW_NUMBER function - TOP doesn't have much context without ORDER BY

这篇关于更新前n个列，其中n来自另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！