如何使用“匿名表”参数化更新？

I'm fairly sure that a big part of the reason I'm failing to find answers with Google is because I don't know the correct terminology to describe my problem.

我很确定我未能找到谷歌答案的一个重要原因是因为我不知道描述我的问题的正确术语。

I know the following form well:

我知道以下形式:

UPDATE Table t
SET t.description = Concat(ot.text, ' text')
FROM Table
INNER JOIN Other_Table ot ON t.id = ot.id
WHERE conditions;

What I'm trying to accomplish is more like:

我想要完成的更像是:

UPDATE Table t
SET t.description = Concat(item, ' text')
FROM t
INNER JOIN (SELECT item FROM ('list', 'of', 'items'))
WHERE conditions;

In other words I have an "anonymous table", or a list of explicit strings specified in the query, rather than stored in a table, and I want them to be picked one at a time and concatenated with some string literal and to update a row.

换句话说,我有一个“匿名表”,或者在查询中指定的显式字符串列表,而不是存储在表中,我希望它们一次被挑选一个并与某些字符串文字连接并更新行。

So imagine I have a table of product information. I want to update this table so the "description" column contains "This product is color!". I'm looking for something like:

所以想象一下我有一张产品信息表。我想更新此表,以便“描述”列包含“此产品是颜色!”。我正在寻找类似的东西:

UPDATE Products p
SET p.desc = Concat('This product is ', color, '!')
FROM p
INNER JOIN (SELECT color FROM ('blue', 'red', 'green'))
WHERE p.sku in (111, 112, 113);

I'm using Access 2010 and connecting to a SQL Server 2008 DB.

我正在使用Access 2010并连接到SQL Server 2008数据库。

2 个解决方案

#1

SQL Server 2008 allows to use the VALUES clause as a table source:

SQL Server 2008允许使用VALUES子句作为表源:

CREATE TABLE products([desc] NVARCHAR(MAX),sku int)


UPDATE p
SET p.[desc] = 'This product is '+ C.color+ '!'
FROM Products p
INNER JOIN (VALUES(111,'blue'),(112, 'red'),(113, 'green')) AS c(sku,color)
ON p.sku = c.sku
WHERE p.sku in (111, 112, 113);

#2

I'm not sure I got you right, but try smth like this

我不确定我是不是让你这么做,但试试像这样的人

update Products p set
    p.desc = Concat('This product is ', C.color, '!')
from p
    inner join
    (
        select 'blue' as color, 111 as id union all
        select 'red' as color, 112 as id union all
        select 'green' as color, 113 as id
    ) as C on C.id = p.sku
where p.sku in (111, 112, 113);

#1