CODEWITHSUNDEEP
mysqlsql
sylar32
sylar32

Reputation: 177

How delete records from table A and B, if B does not contain specific value

I am trying to compose SQL query for deleting data from two tables - A, B. They are connected with a key and only I need to check, if in B is specific value reservation in one column.

Long story short, I have table with rooms and beds and I need to check, if there is any reservation in room. If not, I need to delete room itself and all of its beds.

I've tried two queries, but none of them gives me expected results. Table B is relatively large, so I'm trying to make it as fastest as possible, in one query.

-- gives me always 0 records
DELETE
  a, b
FROM
  rooms a, beds b
WHERE
  a.id = b.id_room AND
NOT EXISTS
  (SELECT 1 FROM beds WHERE id_room = '45' AND state != 'reservation')


-- deletes all records, but rows where state = 'reservation'
DELETE
  a, b
FROM 
  rooms a, beds b
WHERE
  a.id = b.id_room AND id_room = '45' AND state != 'reservation'

Example dataset:

-- rooms
-------------
| id | Name |
-------------
| 1  | 601  |
| 2  | 602  |
| 3  | 603  |

-- beds
-----------------------------|
| id | id_room | state       |
-----------------------------|
| 1  | 1       | free        |
| 2  | 1       | free        |
| 3  | 1       | reservation |
| 4  | 2       | free        |
| 5  | 2       | free        |
| 6  | 3       | reservation |

Expected results:

Rooms 1 and 3 have value reservation in column state in table beds, so it can not be deleted. Room 2 has no reservation, so it can be deleted and it should be deleted including all its beds - so the query should delete record 2 from table rooms and records 4 and 5 from table beds.

I always want to delete only one room (with its beds), so I need to include condition about room ID to calculate with.

Upvotes: 1

Views: 529

Answers (3)

forpas
forpas

Reputation: 164089

This query:

select id_room
from (
  select id_room, state from beds
  union
  select ?, ''
) t 
where id_room = ?
group by id_room
having sum(state = 'reservation') = 0

returns the id of the room that should be deleted, either because there is no 'reservation' for any of its beds or it has no beds.
Replace both ? with the id of the room that you search for.

Join it to the tables in the DELETE statement:

delete r, b
from rooms r 
inner join (
  select id_room
  from (
    select id_room, state from beds
    union
    select ?, ''
  ) t 
  where id_room = ?
  group by id_room
  having sum(state = 'reservation') = 0
) t on t.id_room = r.id
left join beds b
on b.id_room = t.id_room

See the demo.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could try using a left join between the table and check for not matching values

    DELETE  beds, rooms
    FROM  rooms a
    LEFT  JOIN beds b ON a.id = b.id_room 
        AND b.state = 'reservation'
    WHERE b.id_room is null

and based on your code sample

SELECT a.id, b.id 
FROM  rooms a 
LEFT  JOIN beds b ON a.id = b.id_room 
AND b.state = 'reservation' and 
    b.id_room = 45 WHERE b.id_room is null

    DELETE romms, beds
    FROM  rooms a
    LEFT  JOIN beds b ON a.id = b.id_room 
        AND b.state = 'reservation'
    WHERE b.id_room is null
    AND  a.id = 45

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think you want:

DELETE r, b
    FROM rooms r JOIN
         beds b
         ON a.id = b.id_room LEFT JOIN
         (SELECT b2.id_room
          FROM beds b2
          WHERE b2.state = 'reservation'
         ) b2
         ON b2.id_room = b.id_room
    WHERE b2.id_room IS NULL;

Upvotes: 0

Related Questions