Delete rows returned by select?-CodePudding

I have a simple query that inner joins:

select *
    from driver d
    inner join car c on c.id = d.car_id where c.dealership_id=${did} and d.id=${driverId}

This returns the drivers I care about.

I want to delete any driver row returned by that query.

I tried:

delete from driver
where exists (
   select *
        from driver d
        inner join car c on c.id = d.car_id where c.dealership_id=${did} and d.id=${driverId}
)

but that deleted all drivers in the drivers table.

How do I delete only the drivers returned by the select statement?

CodePudding user response：

Using where id in instead of exists will work:

delete from driver
where id in (
   select id
        from driver d
        inner join car c on c.id = d.car_id 
        where c.dealership_id=${did} and d.id=${driverId}
    )

CodePudding user response：

but that deleted all drivers in the drivers table.

Because your condition might all be true by exists part, so it will as same as

DELETE FROM driver

I would use DELETE with JOIN by USING

DELETE FROM driver d
USING car c 
JOIN c.id = d.car_id
WHERE 
 c.dealership_id=${did} and d.id=${driverId}