mysql - 从WHERE子查询SQL Server更新

如何使用UPDATE语句中的子查询？这是我的查询：

UPDATE car_availability
SET    availability_status = 'GOOD'
FROM   car_trip AS TRIP
WHERE  car_availability.car_no = TRIP.car_no
AND NOT EXISTS (SELECT DISTINCT A.car_No
                FROM   car_maintenance A
                WHERE  A.car_no = TRIP.vehicle_id
                AND    start_date = TRIP.workday)
AND EXISTS (SELECT DISTINCT B.car_No
            FROM   car_maintenance B
            WHERE  B.car_no = TRIP.vehicle_id
            AND end_date IS NOT NULL)

我收到错误消息“ UPDATE和DELETE语句中的FROM子句不能包含子查询源或联接。”

我想将Availability_Status更新为GOOD，其中car_maintenance中存在的汽车的start_date等于car_trip的工作日，而car_maintenance中的汽车的结束日期不为空。

最佳答案

正如@McNets所说，您需要将car_availability表添加到FROM子句中。尝试这个：

UPDATE CA
SET    availability_status = 'GOOD'
FROM   car_availability CA
JOIN car_trip AS TRIP ON CA.car_no = TRIP.car_no
WHERE NOT EXISTS (SELECT DISTINCT A.car_No
                FROM   car_maintenance A
                WHERE  A.car_no = TRIP.vehicle_id
                AND    start_date = TRIP.workday)
AND EXISTS (SELECT DISTINCT B.car_No
            FROM   car_maintenance B
            WHERE  B.car_no = TRIP.vehicle_id
            AND end_date IS NOT NULL)