UPDATE FROM WHERE Subquery SQL Server

Question

How can I use a subquery from an UPDATE statement? Here is my query:

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)

I get an error 'FROM clause in UPDATE and DELETE statements cannot contain subquery sources or joins.'

I want to update availability_status to GOOD in which car exists in car_maintenance with its start_date is equal to workday of car_trip and cars which is in car_maintenance with its end date is not null.

This is a duplicate question, you get an answer here: http://stackoverflow.com/a/2334741/3270427 — McNets, Mar 10 '17 at 15:40
@McNets - I can't use INNER JOIN also. I'm using SQL Server 2016. — Robert John Martin, Mar 10 '17 at 16:32
Read it carefully, you must add `car_availability` to FROM clause — McNets, Mar 10 '17 at 16:34

score 0 · Accepted Answer · answered Mar 10 '17 at 18:04

As @McNets said, you need to add the car_availability table to your FROM clause. Try this:

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)

UPDATE FROM WHERE Subquery SQL Server

1 Answers1