UPDATE SET 中的子查询(sql server 2005) [英] subqueries in UPDATE SET (sql server 2005)

问题描述

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

I have a question about using subqueries in an Update statement. My example:

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

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

Is it possible to refer to main table value (trips.guid) in subqueries?

当我尝试使用 trips.guid 时出现错误:

When i try to use trips.guid I get the error:

无法绑定多部分标识符trips.guid"."

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

select Distinct city from poi"子句返回的不止一个城市.

The clause 'select Distinct city from poi' return more that one city.

推荐答案

你可以尝试类似的事情

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

这篇关于UPDATE SET 中的子查询(sql server 2005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！