subqueries in UPDATE SET (sql server 2005)

Question

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) 

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

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

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

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

Answer 1

You can use constants and values from the outer select in the sub-select:

Update trips
Set locations = ( Select Distinct trips.city + ', ' + poi.city
                  From poi
                  Where poi.trip_guid = trips.guid )

We don't know how your tables look like, so I can only assume that Distinct will work for you that way (returning only one distinct city in the sub-query).

Answer 2

You can try something like

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

Answer 3

Another version.

UPDATE trips
SET locations = trips.city + ', ' + poi.city
FROM trips INNER JOIN poi
ON poi.trip_guid = trips.guid

Answer 4

I found the solution - just move the subquery to the UDF :)

UPDATE TRIPS
   SET locations = getAllTripCity(guid);

My UDF's source code:

CREATE FUNCTION dbo.getAllTripCity(
    @tripGuid uniqueidentifier
)
RETURNS nvarchar(200)
AS
BEGIN
 DECLARE @cities nvarchar(200);
 set  @cities = ''
 select @cities =  @cities + city + ' ' from (select DISTINCT city poi where poi.trip_guid = @tripGuid)
 return @ @cities;
END

That's all what you I need to do - works fine :)