sql-server-2005 - UPDATE SET 中的子查询(sql server 2005)

Question

我有一个关于在 Update 语句中使用子查询的问题。我的例子:

UPDATE TRIPS
   SET locations = city + ', ' FROM (select Distinct city 
                                       from poi 
                                      where poi.trip_guid = trips.guid) 

是否可以在子查询中引用主表值 (trips.guid)？

当我尝试使用 trips.guid我收到错误:

"The multi-part identifier "trips.guid" could not be bound."

条款“从 poi 中选择不同的城市”返回的不仅仅是一个城市。

Answer 1

你可以尝试类似的东西

UPDATE  trips
SET     locations = t.city + ', ' + poi.city
FROM    trips t INNER JOIN
        (
            select Distinct city, trip_guid from poi
        ) poi ON t.trip_guid = poi.trip_guid