SQL Query error - what do I do wrong?

Here is SQL UPDATE I'm trying to execute:

UPDATE T
  SET T.CurrentStopNumber = TS.CurrentStopNumber 
 FROM Trip T 
INNER JOIN (SELECT TripId, MIN(StopNumber) CurrentStopNumber
             FROM TripStop
            WHERE TripId = '106504'
              AND (IsPickup = 1 OR IsDrop = 1)
              AND StopNumber > (SELECT COALESCE(max(StopNumber), 0) 
                                  FROM TripUpdate 
                                 WHERE TripId = '106504' 
                                   A开发者_Go百科ND Type = 2)) TS ON T.TripId = TS.TripId

I get error on second line:

 /* Error message: SQL script is wrong mismatched input . expecting "EQ" */

I am familiar with SQL Server and I'm sure it would run on SQL Server just fine. Subquery runs fine and returns 1 row as I expect. I just need to update table with that value. What is wrong?

Use:

UPDATE TRIP
   SET currentstopnumber = (SELECT MIN(ts.stopnumber)
                              FROM TRIPSTOP ts
                             WHERE ts.tripid = TRIP.tripid
                               AND ts.tripid = '106504'
                               AND 1 IN (ts.ispickup, ts.isdrop)
                               AND ts.stopnumber > (SELECT COALESCE(MAX(tu.stopnumber), 0)
                                                      FROM TRIPUPDATE tu
                                                     WHERE tu.tripid = ts.tripid
                                                       AND tu.type = 2)
                          GROUP BY ts.tripid)
 WHERE EXISTS (SELECT NULL 
                 FROM TRIPSTOP ts
                WHERE ts.tripid = TRIP.tripid
                  AND ts.tripid = '106504'
                  AND 1 IN (ts.ispickup, ts.isdrop)
                  AND ts.stopnumber > (SELECT COALESCE(MAX(tu.stopnumber), 0)
                                         FROM TRIPUPDATE tu
                                        WHERE tu.tripid = ts.tripid
                                          AND tu.type = 2))

The JOIN is preferable, but the syntax isn't widely supported.

I don't think that you should specify the table for the fields that you set, as the database already knows which table to update:

SET CurrentStopNumber = TS.CurrentStopNumber

(IIRC, that is not allowed in SQL Server either.)