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.

Is there guaranteed to be only 1 city in poi for a trip_guid (or are you expecting it to handle this for you somehow)? — Martin Smith, Apr 06 '10 at 16:34
There are at least 2 cites. The idea is to put all cites in one field. — itdeveloper, Apr 08 '10 at 07:56
Your example works in Oracle. For SQL Server the other answers are applicable. — Monstieur, Jun 18 '13 at 07:40

Adriaan Stander · Accepted Answer · 2010-04-06T16:46:15.563

29

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

edited Apr 06 '10 at 16:46

answered Apr 06 '10 at 16:36

Adriaan Stander

162,879
31
289
284

5

+1 although I don't agree with formatting - would put the `INNER JOIN` into the next line ;-) – Peter Lang Apr 06 '10 at 16:45

score 9 · Answer 2 · answered Apr 06 '10 at 16:48

9

Another version.

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

answered Apr 06 '10 at 16:48

Martin Smith

438,706
87
741
845

Peter Lang · Answer 3 · 2010-04-06T16:41:35.560

4

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).

edited Apr 06 '10 at 16:41

answered Apr 06 '10 at 16:30

Peter Lang

54,264
27
148
161

4

The subquery will break if it returns more than 1 record – Jimmie R. Houts Apr 06 '10 at 16:35
@Jimmie R. Houts: Correct. I was updating my answer the moment you posted your comment, thanks! – Peter Lang Apr 06 '10 at 16:36
Did you actualy try this, it says **Incorrect syntax near 't'.** – Adriaan Stander Apr 06 '10 at 16:39
@astander: Thanks for testing, removed the aliases and now it works for me. – Peter Lang Apr 07 '10 at 06:18
@itdebeloper: Is there also a `city` field in `trips` or do you just want to concatenate the `city`-fields of `poi` separated by comma? – Peter Lang Apr 08 '10 at 08:25
I just want to concatenate the city fields of poi. – itdeveloper Apr 08 '10 at 12:03
@itdebeloper: This sounds quite different than your question. Have a look at this similar question: http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery – Peter Lang Apr 08 '10 at 13:26

score 1 · Answer 4 · answered May 22 '10 at 13:49

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 :)

score 0 · Answer 5 · answered Dec 04 '13 at 09:05

I had the same issue as the initial poster. My use case was the following: One table contained Date and Time of a sport event. Because I am getting information from different sources, I changed the schema of the database so I had a int value for time and datetime (or maybe just date) for the date of the sport event.

This is my query:

UPDATE Matches 
SET StartTime= MatchTime.ThisMatchStartTime
FROM Matches AS M
INNER JOIN (SELECT CONVERT(int, CONVERT(varchar, DATEPART(Hour, MatchDate)) + RIGHT('00' + CONVERT(varchar, DATEPART(Minute, MatchDate)),2)) AS ThisMatchStartTime, MatchId
  FROM [Matches]
  WHERE SportTypeId=16) AS MatchTime ON M.MatchId=MatchTime.MatchId
WHERE StartTime > 2400
AND SportTypeId = 16;

Some explanation: You have to give the subquery MatchStartTime a different name otherwise you get a warning/error from SQL Server. I also had to add MatchId so I knew I was updating the correct Match. The SportTypeId is used to separate different sports in the database.

Thanks to @astander for pointing me in the right direction. Without his post I would have struggled a bit more to end up with this solution.

subqueries in UPDATE SET (sql server 2005)

5 Answers5

Linked